Adodb to ado.net

[LuxCoder]

Hello,

I went through many ado.net documentation but this doesn't seem to get into my matter somehow. It'll be great if anyone can show me how to execute the following function using ado.net coding:

Public prs As New ADODB.Recordset
Public crs As New ADODB.Recordset


prs.Open("select cuid from customer order by cuid", Module1.db, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
        If prs.RecordCount > 0 Then
            prs.MoveLast()
            cuselect = prs.Fields!cuid.Value
prs.close
End If

crs.Open("select * from customer where cuid ='" & cuselect & "'", Module1.db, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)

Text1.Text = crs.Fields!custid.Value.ToString
Combo1.Text = crs.Fields!Name.Value.ToString
Text2.Text=CDate(crs.Fields!createdon.Value).ToString("dd/MMMyy,HH:mm")

crs.close

[kleinma]

Is that code just to pull the most recent record from the customers table?

[LuxCoder]

Yes sir.

[LuxCoder]

Also, i am using SQL server express edition.

[kleinma]

Try this. I don't have your data store to test it against, but I think the syntax is all correct.

        Dim myDBConnection As SqlClient.SqlConnection = Nothing
        Dim myDBCommand As SqlClient.SqlCommand = Nothing
        Dim myDBReader As SqlClient.SqlDataReader = Nothing
        Try
            myDBConnection = New SqlClient.SqlConnection("CONNECTION STRING GOES HERE")
            myDBCommand = New SqlClient.SqlCommand("SELECT * FROM customer WHERE cuid = (Select MAX(cuid) from customer)", myDBConnection)
            myDBReader = myDBCommand.ExecuteReader(CommandBehavior.CloseConnection)

            If myDBReader.HasRows Then
                text1.Text = myDBReader.Item("custid").ToString
                Combo1.Text = myDBReader.Item("Name").ToString
                text2.text = CDate(myDBReader.Item("createdon")).ToString("dd/MMMyy,HH:mm")
            Else
                'HANDLE CASE FOR NO RECORDS EXIST IN DB
            End If
        Catch ex As Exception
            'HANDLE EXCEPTION HERE
        Finally
            'CLEAN UP DB OBJECTS
            If myDBReader IsNot Nothing Then myDBReader.Close()
            If myDBCommand IsNot Nothing Then myDBCommand.Dispose()
            If myDBConnection IsNot Nothing Then myDBConnection.Dispose()
        End Try

[Campion]

Kleinma beat me to it, and was much more thorough.

Drat the lack of a VS machine right now.

[LuxCoder]

On catching exception it shows this error:

Object reference not set to an instance of an object.

[kleinma]

Well if you copy/pasted my code then make sure that

1) custid, Name, and createdon are actually your column names, and verify casing. I don't think they are case sensitive in this scenario, but off the top of my head I can't remember

2) make sure you replaced the CONNECTION STRING GOES HERE text with a valid connection string to your SQL database.

3) I think I forgot to add a call to myDBConnection.Open() to open the DB connection. Place that just before the line to execute the reader.

4) if all the above don't fix it, set a break point on the Try block, and step through to see exactly which line makes the exception occur.

[LuxCoder]

I am not getting the value of ClientId in the MsgBox.

Dim myDBConnection As SqlClient.SqlConnection = Nothing
        Dim myDBCommand As SqlClient.SqlCommand = Nothing
        Dim myDBReader As SqlClient.SqlDataReader = Nothing

        Try
            myDBConnection = New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\ProjectMW\MachineWale\MachineWale\DatabaseMW.mdf;Integrated Security=True;User Instance=True")
            myDBConnection.Open()
            myDBCommand = New SqlClient.SqlCommand("SELECT * FROM ClientProfile", myDBConnection)
            ' WHERE ClientID)= (Select MAX(ClientID) from ClientProfile)", 
            myDBReader = myDBCommand.ExecuteReader(CommandBehavior.CloseConnection)
            If myDBReader.HasRows Then

                MsgBox(myDBReader.Item("ClientID"), MsgBoxStyle.OkOnly)

                Me.TextBox1.Text = myDBReader.Item("ClientID").ToString
                Me.ComboBox2.Text = myDBReader.Item("machineid").ToString
                'TextBox2.Text = CDate(myDBReader.Item("firstname")).ToString("dd/MMMyy,HH:mm")
            Else
                'HANDLE CASE FOR NO RECORDS EXIST IN DB
            End If
        Catch ex As Exception
            'HANDLE EXCEPTION HERE
        Finally
            'CLEAN UP DB OBJECTS
            If myDBReader IsNot Nothing Then myDBReader.Close()
            If myDBCommand IsNot Nothing Then myDBCommand.Dispose()
            If myDBConnection IsNot Nothing Then myDBConnection.Close()
        End Try

[kleinma]

Sorry, it was the fact that I didn't test the code of course

You need a call to .read() of the datareader so it will advance to the first record (even if there is only 1 record returned by your query) so that it can read data from the record.

Try this. Note the call to .read() before trying to get the data. This also sort of assumes you will only get 1 record back when you run your query, considering your goal is to get the most recent record.

        Dim myDBConnection As SqlClient.SqlConnection = Nothing
        Dim myDBCommand As SqlClient.SqlCommand = Nothing
        Dim myDBReader As SqlClient.SqlDataReader = Nothing

        Try
            myDBConnection = New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\ProjectMW\MachineWale\MachineWale\DatabaseMW.mdf;Integrated Security=True;User Instance=True")
            myDBCommand = New SqlClient.SqlCommand("SELECT * FROM ClientProfile WHERE ClientID = (Select MAX(ClientID) from ClientProfile)", myDBConnection)

            myDBConnection.Open()
            myDBReader = myDBCommand.ExecuteReader(CommandBehavior.CloseConnection)

            If myDBReader.HasRows Then

                myDBReader.Read()

                MsgBox(myDBReader.Item("ClientID"), MsgBoxStyle.OkOnly)

                Me.TextBox1.Text = myDBReader.Item("ClientID").ToString
                Me.ComboBox2.Text = myDBReader.Item("machineid").ToString
                'TextBox2.Text = CDate(myDBReader.Item("firstname")).ToString("dd/MMMyy,HH:mm")
            Else
                'HANDLE CASE FOR NO RECORDS EXIST IN DB
            End If
        Catch ex As Exception
            'HANDLE EXCEPTION HERE
        Finally
            'CLEAN UP DB OBJECTS
            If myDBReader IsNot Nothing Then myDBReader.Close()
            If myDBCommand IsNot Nothing Then myDBCommand.Dispose()
            If myDBConnection IsNot Nothing Then myDBConnection.Close()
        End Try

[LuxCoder]

wow, that worked like a pie.... thank you so much.
just one more thing sir.
can u also show me how to delete and update it??

[LuxCoder]

Anyone please??? i am waiting!

[Campion]

Just use UPDATE and DELETE SQL syntax, and apply the string by using Command.ExecuteNonQuery. If you know SQL, then writing the SQL string should be easy.

[LuxCoder]

Can you give me an example?? please!

[LuxCoder]

I tried using this but its not saving the text in textbox5

Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\ProjectMW\MachineWale\MachineWale\DatabaseMW.mdf;Integrated Security=True;User Instance=True")
            Using command As New SqlCommand("INSERT INTO ClientProfile (FirstName) VALUES'" & TextBox5.Text & "'", connection)
                               connection.Open()
                command.ExecuteNonQuery()
            End Using
        End Using

[kleinma]

Well just by looking at the code, you don't have a space between VALUES and the '

Are you getting any sort of exception thrown when you run the code?