SQL Question

[liquidspaces]

Could somebody post the proper way to say:

 

("Select from TABLE_NAME where NAME = txtName.text  AND ADDRESS = txtAddress.text", connectionString)

 

I'm not sure how to treat field names here, but I'm pretty sure that I'm not doing it right. I've tried treating them every way I can think of, but no luck so far.

 

Thanks in advance,

Kevin

[Robby]

Also, instead of the * you can use all the field names you will need, ie FirstName, LastName, Phone, Address

("Select * from TABLE_NAME where NAME = '" & txtName.text & "' AND ADDRESS = '" & txtAddress.text & "'", connectionString)

[liquidspaces]

Thanks, Robby I'll give it a shot. I didn't expect to treat textboxes as strings, but I suppose that makes sense. I'll let you know how it pans out:)

[Robby]

Note that all variables must be surrounded by the & and "

 

The only time you should also use the single quote as I did in the above sample IS when it's a string (both text box or a string variable)

 

If it is a numeric field then you would do something like this...

 

dim intValue as integer = 12
("Select * from TABLE_NAME where NAME = '" & txtName.text & "' AND SomeNumberField = " & intValue , connectionString)

[liquidspaces]

That definitely helped me, but I suppose that I have other problems as well. I'm trying to return the primary key where certain conditions are met. The actual SQL string is much longer, but this should give you an idea:

 

Dim objDataAdapter As New OleDb.OleDbDataAdapter("Select PRIMARY_KEY from MyTable WHERE NAME = '" & txtName.text & "' AND ADDRESS = '" & txtaddress.text & "'", OleDbConnection1)

Dim objCommand As New OleDb.OleDbCommandBuilder(objDataAdapter)
       
Dim objDataSet As New DataSet()

objDataSet.Clear()
objDataAdapter.FillSchema(objDataSet,SchemaType.Source, "MyTable")

objDataAdapter.Fill(objDataSet, "MyTable")
       
Dim intBOLN As Integer
intBOLN = objDataAdapter.Fill(objDataSet, "MyTable")

MsgBox("The primary key is" & intBOLN)

 

At the moment this is returning the row number instead of the primary key value. Does anything inparticular catch your eye as being wrong?

[Robby]

If all you really want is the value of field PRIMARY_KEY then you don't need a Dataset or a CommandBuilder.

 

With your code, what happens if there is more than one row containing that Name and Address?

[liquidspaces]

There very well may be a better way to do this. This is the reason for all this:

 

The user enters data. The user saves data. It is imperative that they know the primary key at that point for searching purposes. What is the best way to find and display it?

[Robby]

try this..

   Private Function GetTableValue() As Integer
       Dim drSqlReader As OleDbDataReader
       Dim SqlCMD As OleDbCommand
       Dim SqlCN As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c\test.mdb;User ID=;Password=;")
       Dim strSql As String, intTemp As Integer
       Try
           If SqlCN.State = ConnectionState.Closed Then SqlCN.Open()
           strSql = "SELECT PRIMARY_KEY FROM MyTable  WHERE NAME = '" & txtName.text & "' AND ADDRESS = '" & txtaddress.text & "'"
           SqlCMD = New OleDbCommand(strSql, SqlCN)
           drSqlReader = SqlCMD.ExecuteReader()

           If drSqlReader.Read Then
               intTemp = DirectCast(drSqlReader.Item("PRIMARY_KEY"), Integer)
           End If
       Catch
           intTemp = -1
       Finally
           If Not SqlCN.State = ConnectionState.Closed Then SqlCN.Close()
           If Not drSqlReader.IsClosed Then drSqlReader.Close()
           If Not SqlCMD Is Nothing Then SqlCMD.Dispose()
       End Try
       Return intTemp
   End Function