run sql command when using the dataset wizard

[DustyCarpet]

Hi

I have added a bound datatable from a dataset created with the wizard and dragging and dropping the datatable. Rather than use the binging navigator I have added my own edit/add/delete buttons.

When I click add I would like to set the ID text box to the max ID +1 from the sql table.
If had built the sql connection my self i would do something like:

Dim maxIdCommand As SqlCommand = New SqlCommand _
          ("SELECT MAX(BRCID) AS MaxID " & _
         "FROM [VOLUNTEERS].[ContactDetails]", objConnection)
        ' Open the connection, execute the command
        objConnection.Open()
        Dim maxId As Object = maxIdCommand.ExecuteScalar()
        txtBRCID.Text = maxId + 1


        ' Close the connection..
        objConnection.Close()

However when its done with a wizard i don't even know the name of the variable name for the connection string to run a sql command. Any ideas how I can run a sql command or another method to do this.

thanks

 

[JohnH]

Open DataSet designer and right-click the TableAdapter, select "Add Query...".

 

[jmcilhinney]

Open DataSet designer and right-click the TableAdapter, select "Add Query...".

That will work if the query you're adding produces a result set with the same schema as the existing DataTable. In this case, the query result set doesn't correspond to a table schema so you can't add the query to an existing table adapter. You'd have to add a new adapter that doesn't correspond to any table first and add the query to that. I believe that you can do both in one go in the DataSet designer.

That said, there's no need to add any query in this case anyway. What the OP is trying to achieve should not be done. DO NOT generate your own ID by incrementing the current maximum value. Just make your PK column and identity and the database will generate the ID when you insert the record.

 

[DustyCarpet]

thank you****************