[RESOLVED] [2005] Noob looking for a better way.

[Besoup]

Hey all,
I am just recreating a vb6 function that I used to call in another app. I am very new to the whole .NET framework, but I do think there has to be a better way than what I came up with. This function queries a table for an invoice number, returns that invoice number and then increments the table.

This is the code that I got working there's gotta be a better way:

Private Function GetInvoiceNo() As Long
 
        sSQL = "SELECT Order_Ctrl_Online from Order_ctrl"
        da = New SqlClient.SqlDataAdapter(sSQL, conn)
        da.Fill(ds, "InvoiceNo")
        GetInvoiceNo = ds.Tables("InvoiceNo").Rows(0).Item(0)
        ds.Tables("InvoiceNo").Rows(0).Item(0) = ds.Tables("InvoiceNo").Rows(0).Item(0) + 1
        sSQL = "Update Order_Ctrl SET Order_Ctrl_Online = Order_Ctrl_Online + 1"
        da = New SqlClient.SqlDataAdapter(sSQL, conn)
        da.UpdateCommand = New SqlCommand(sSQL, conn)
        conn.Open()
        da.UpdateCommand.ExecuteNonQuery()
        conn.Close()
    End Function

[wild_bill]

Dim returnValue As Long
 
        conn.Open()
        'get old value
        Dim cmd As New SqlCommand("SELECT Order_Ctrl_Online from Order_ctrl", conn)
        returnValue = cmd.ExecuteScalar
        'increment old value
        cmd.CommandText = "Update Order_Ctrl SET Order_Ctrl_Online = Order_Ctrl_Online + 1"
        cmd.ExecuteNonQuery()
        conn.Close()
 
        Return returnValue

[Besoup]

That works good in this situation, but what if we complicated it and pulled a record of like 20+ fields and needed to change values in all of them?

Thanks for your reply!

[onlyGirl]

then you would use a loop

[Besoup]

Dim returnValue As Long
 
        conn.Open()
        'get old value
        Dim cmd As New SqlCommand("SELECT Order_Ctrl_Online from Order_ctrl", conn)
        returnValue = cmd.ExecuteScalar
        'increment old value
        cmd.CommandText = "Update Order_Ctrl SET Order_Ctrl_Online = Order_Ctrl_Online + 1"
        cmd.ExecuteNonQuery()
        conn.Close()
 
        Return returnValue

Just tried this and it says errors:
ExecuteScalar: Connection property has not been initialized.

[Besoup]

This is the exact code I am getting the error with:

Private Function GetInvoiceNo() As Long
        conn.Open()
        Dim cmd As New SqlCommand("SELECT Order_Ctrl_Online from Order_ctrl")
        GetInvoiceNo = cmd.ExecuteScalar
        cmd.CommandText = "Update Order_Ctrl SET Order_Ctrl_Online = Order_Ctrl_Online + 1"
        cmd.ExecuteNonQuery()
        conn.Close()
    End Function

[jmcilhinney]

So initialise the Connection property. You can't access a database using a Command if you don't have a Connection to connect to the database over. You can't talk to someone over the telephone if you don't have a telephone line.

[Besoup]

I initialized it publicly at the top of the class..

[GaryMazzone]

You need to set the command objects connection property to that connection.

[jmcilhinney]

As Gary says. You may have created a Connection object but you have not set the Connection property of your Command. When you execute the Command, how is it to know what Connection it's supposed to use?

[Besoup]

ahh gotcha... thanks guys!

[VBCrazyCoder]

Don't forget the Try Catch and make sure the connection gets closed if an error is thrown. You can also use the "Using" block in VB 2005 for the connection object so you can be sure that it releases it resources. Just thought I would mention this :-)