SQL Question

[liquidspaces]

Could somebody post the proper way to say:

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

Im not sure how to treat field names here, but Im pretty sure that Im not doing it right. Ive 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 Ill give it a shot. I didnt expect to treat textboxes as strings, but I suppose that makes sense. Ill 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 its 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. Im 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 dont 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