Need step-by-step SQL help

[shadowfyre]

So my VB book only shows how to setup an SQL query at design time. I need to do it at run time.

My program is just a little thing that allows a person to copy a file from their desktop to the network. The user would put the filename to be copied in a textbox. There is an Access database sitting on the network that maps all the files to particular directories. So when the user clicks on submit, the program looks up what directory to put it in and copies it. I'm stuck on looking up the directory to put it in.

For development purposes, I have a copy of the Access database on my harddrive.

I know I need a connection, a data adapter, and a dataset. How do I connect to the database on my hard drive? I've looked at a lot of code in other threads here, but I am lost. I think I need something like (this is just off the top of my head, I haven't tried this):

Dim dir As String
Dim conn As New SqlConnection("c:\DB\SRCDATA.MDB")
Dim query As String = "SELECT LockDirectory FROM MstrFileList WHERE MstrFileName = " & TextBox1.Text
Dim cmd = New SqlCommand(query, conn)
conn.Open()
dir = cmd.ExecuteNonQuery()
conn.Close()
MsgBox("The directory is " & dir)

As I said, I haven't actually tried this yet because I am so lost. Am I even on the right track? If this is completely off, could someone kind of step me through it?

Can I put the path in the SqlConnection?

Will the concatenation for query work?

Did I declare cmd properly?

Can I assign the cmd.ExecuteNonQuery to dir like that? Is the ExecuteNonQuery even correct?

?????

Thanks

[hellswraith]

You are using access, so don't use the SqlConnection object. You will want to use the OleDbConnection object. Same with dataadapters, datareaders, etc.

[shadowfyre]

Do I explicitly declare the dataadapters and datareaders? If so, where and how?

[Pirate]

Originally posted by shadowfyre
Do I explicitly declare the dataadapters and datareaders? If so, where and how?

You need to read more about ADO.NET here : http://samples.gotdotnet.com/quickstart/winforms/

[shadowfyre]

Thanks for the link.

One thing that I'm still confused on though.

Here is what I have so far:

Dim oOleDbConnection As OleDb.OleDbConnection
            Dim sConnString As String = _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\DB\SRCDATA.MDB;" & _
                "User ID=Admin;" & _
                "Password="
            Dim cmd As String = "SELECT LockDirectory FROM MasterFileList WHERE MstrFileName = " _
                & TextBox1.Text
            Dim mySqlCommand As SqlCommand
 
            oOleDbConnection = New OleDb.OleDbConnection(sConnString)
            oOleDbConnection.Open()
            mySqlCommand = New SqlCommand(cmd)
            mySqlCommand.ExecuteNonQuery()

It is not complete. My main question is, do I use the ExecuteNonQuery method or should I use the ExecuteReader method to get the result?

Also, how do I assign the result, a directory path, to a usuable variable to use in my program? I haven't seen any examples that do that.

[Pirate]

If you want to to get data from the database , then use DataReader like this :

While (adoReader.Read)
            Str = adoReader.Item(ColumnString)            
End While

if you are deleting or update (meaning you're not getting data)
then use OleCommand object like this :

If objCmd.ExecuteNonQuery() Then
 'Executed 
 
  Else
'Can't execute 
 
End If

[shadowfyre]

Alright. I think I'm getting this. Ran into another problem, though.

I'm getting an error when it tries to run the ExecuteReader method. It says:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: ExecuteReader: Connection property has not been initialized.

Dim oOleDbConnection As OleDb.OleDbConnection
Dim sConnString As String = _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\DB\SRCDATA.MDB;" & _
                "User ID=Admin;" & _
                "Password="
Dim myReader As SqlDataReader
Dim cmd As String = "SELECT LockDirectory FROM MasterFileList WHERE MstrFileName = " _
                & TextBox1.Text
Dim mySqlCommand As SqlCommand
Dim dir As String
 
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()
mySqlCommand = New SqlCommand(cmd)
myReader = mySqlCommand.ExecuteReader()    'This is where the error comes from
dir = myReader.Item(0)
myReader.Close()
oOleDbConnection.Close()

Any ideas on how to fix the error? Where should the connection property be initiailized?

Also, the parameters for the myReader.Item method want an integer. Since this should only return a single result, I put 0 in instead of the column name. Is that correct?

[Pirate]

First off , why are you using Sql objs instead of using OleDb which is perfectly for mdb databases .sql classes are optimized versions of ole for SQL Servers though it can be used with mdb but not recommended .


Then try this .

Private Sub CallMe()
        Dim dir As String
        Dim sConnString As String = _
                                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=C:\DB\SRCDATA.MDB;" & _
                                "User ID=Admin;" & _
                                "Password="
 
        Dim oOleDbConnection As New OleDbConnection(sConnString)
        Dim cmd As String = "SELECT LockDirectory FROM MasterFileList WHERE MstrFileName = " _
        & TextBox1.Text
        Dim myReader As OleDbDataReader
 
        Try
 
            oOleDbConnection.Open()
            Dim myOleCommand As New OleDbCommand(cmd, oOleDbConnection)
            myReader = myOleCommand.ExecuteReader()    'This is where the error comes from
            While (myReader.Read)
                dir = myReader.Item(0)
            End While
 
        Catch ex As Exception
            MessageBox.Show(ex.Message) ' if any error then send me this error message
            myReader.Close()
            oOleDbConnection.Close()
        End Try
        
    End Sub

[Pirate]

Also, the parameters for the myReader.Item method want an integer. Since this should only return a single result, I put 0 in instead of the column name. Is that correct?

myReader(column) is equal to myReader.Item(column) .
You can replace 'column' with the number of colmn in the database (in your case you put 0 which means it's the first column in the table 'MasterFileList' ) or you can use string value that represents the column name . Both works but to make sure everything work well type the name of the column you are retrieving from .

[shadowfyre]

Thanks. There were a lot of DUH's in your code, stuff that I should have caught that I didn't. Namely the Ole vs. sql. It was late Friday afternoon. Does that a good excuse?

Anyway. Here is my udated code (thanks Pirate):

'---------------- SQL stuff ----------------
            Dim sConnString As String = _
                "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\DB\SRCDATA.MDB;" & _
                "User ID=Admin;" & _
                "Password="
            Dim oOleDbConnection As New OleDb.OleDbConnection(sConnString)
            Dim myReader As OleDb.OleDbDataReader
            Dim cmd As String = "SELECT LockDirectory FROM MasterFileList WHERE MstrFileName = " _
                & filename.ToUpper
            Dim dir As String
 
            Try
                oOleDbConnection.Open()
                Dim myOleCommand = New OleDb.OleDbCommand(cmd, oOleDbConnection)
                myReader = myOleCommand.executereader()
                While (myReader.Read)
                    dir = myReader.Item("LockDirectory")
                End While
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Caught Exception")
                myReader.Close()
                oOleDbConnection.Close()
            End Try
            myReader.Close()
            oOleDbConnection.Close()
            '----------------- SQL stuff --------------------

Here is what the Catch statement sends:

No value given for one or more required parameters.

Once I click OK, I get the VS generated error:

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

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

and the "myReader.Close() command is highlighted.

I wish the ex.Message was a bit more specific, so I could figure out which method I'm missing parameters for.

[shadowfyre]

Got it! I needed to add some quotes around the filename and add a semi colon at the end of my query.

Works great now. Thanks.

[Pirate]

Ok . Here is a working demo showing you how to use OleDataReader . You need VB.NET 2003 , XP (or 2000) MS Access , Winrar to unzip the project. Tell me if you're stuck in any part .

[Pirate]

Originally posted by shadowfyre
Got it! I needed to add some quotes around the filename and add a semi colon at the end of my query.
Works great now. Thanks.

Opps , I didn't know that you got it till I posted the demo . Glad it's working now .