SQL Statement Variables In Class Area

[smilbuta]

Im trying to generate a dataset based on a variable in a SQL statment. I can do it while inside a sub but in the class root i cannot figure out how to do this. The Item In bold is what i would like to have as a variable.. so here is my sample statment

Imports System.Data
Imports System.Data.SqlClient

Public Class DonorDB
Inherits System.Windows.Forms.Form
Dim objConnection As SqlConnection = New SqlConnection("My server connection Bla BLa")
Dim objDataAdapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT DonorID, Organization, Name" & _
"FROM Donors WHERE Donors.Organization = A VARIABLE " & _
"ORDER BY Name", objConnection)

Dim objDataSet As DataSet
Dim objDataView As DataView
Dim objCurrencyManager As CurrencyManager


Private sub somesub()
Some Function....
End SUb

End Class

[kleinma]

Dim objDataAdapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT DonorID, Organization, Name" & _
" FROM Donors WHERE Donors.Organization = '" & mOrganization & "'" & _
" ORDER BY Name", objConnection)

where mOrganization is the variable name...

this is also assuming that Donors.Organization is text data... thats why the single tick marks are there to wrap it... also if there is a chance that organization can have a ' in it.. that will screw everything up... in which case use

" FROM Donors WHERE Donors.Organization = '" & mOrganization.Replace("'","''") & "'" & _

try that... also be sure to include spaces when doing new lines like you are.. otherwise the SQL will be spit out like
"Select DonorID, Organization, NameFROM Doners" ...etc...

[smilbuta]

Thank you for the SUPER fast Responce....

[Briantcva]

May want to consider string.Format(). Much easier to read and debug. Consider the following:

"INSERT INTO Users (Value1, Value2) VALUES ('" & var1 & "'," & var2 & "')"

vs.

string.Format ("INSERT INTO Users (Value1, Value2) VALUES ('{0}','{1}'),var1,var2)

That, along w/ changing the default colors to differentiate strings, numbers, and operators, has made my code far easier to read.

[smilbuta]

Ok I created the String Correctly and Execute the code but i Recieve an Error. Here is my Actual code for the portion of the application in question.

In short my variable is a Text Field In the parent form. This child Form is launched from Parent Form and Uses the Text to populate the dataset in the child.

Dim DonorNumber As String = objDonorDB.TextBox1.Text

Dim objConnectionGifts As SqlConnection = New SqlConnection("My server connection parms.")
Dim objDataAdapterGifts As SqlDataAdapter = New SqlDataAdapter( _
"SELECT Donors.DonorID, FirstName, LastName, Gifts.GiftID, GiftType, GiftRecDate, GiftAckDate, GiftNotes " & _
"FROM Donors, Gifts WHERE Gifts.DonorID = '" & DonorNumber & "'" & _
"ORDER BY Gifts.GiftID", objConnectionGifts)


Dim objDataSetGifts As DataSet
Dim objDataViewGifts As DataView
Dim objCurrencyManagerGifts As CurrencyManager


I am also using a Module to Share the data across the forms. If there is a better way Help would be apreciated.

Imports System.Data
Imports System.Data.SqlClient

Module DonorGiftModule
Friend objFullDonor As FullDonor
Friend objGiftView As GiftView
Friend objDonorDB As DonorDB
End Module

And finaly this is the Error Message I Recieve:

An unhandled exception of type 'System.NullReferenceException' occurred in GiftsInterface.exe

Additional information: Object reference not set to an instance of an object.

So What am i Missing?

[kleinma]

perhaps an object you are using is not being created via a constructor?

that is usually what that error points to. Set a break point in your code and run through it until you hit the error and see what object is not instanciated

[smilbuta]

In the Form Load event for "Donor" i have the folowing Instances..

Dim objDataView as New DataView()
objDonorDB = Me.

I assumed looking at the code as you said an instance was missing somewhere so in GiftView form i placed the following line in the CLASS section of the code.

Dim objDonorDB as New DonorDB()

The application ran but apparently if I understand what I did (self LOL) i created a new instance of the DonorDB and the txtDonor field had no data because my test box to show the passed value was now empty. It was populated with a value in the parent form tho.