SQLDataAdapter Update problem

[IanPikeBioquell]

I am a newbie to VB 2008 Express and trying to get a simple application working with SQL Server.

I have a form which i use to insert new records and that is working fine. The table has an IDENTITY column in it. However when i do an insert into the table the data is there but the form does not display the new IDENTITY number.

See code extract below. I hope it makes sense and that someone may be able to help me.

Thanks in advance.


mySqlConnection = New SqlConnection(ConnectionString)
mySqlAdapter = New SqlDataAdapter("SELECT * FROM bsp_ChgReq", mySqlConnection)
mySqlAdapter.Fill(mySqlDataSet, "bsp_ChgReq")



Dim bsp_AddedBy = Form1.txtAddedBy.Text
Dim bsp_DateAdded As DateTime = Form1.txtDateAdded.Text
Dim bsp_ShortDesc = Form1.txtDescription.Text
Dim bsp_ReqBy As DateTime = Form1.txtReqBy.Text
Dim bsp_EstBy As DateTime = Form1.txtEstBy.Text
Dim bsp_AppBy = Form1.txtAppBy.Text
Dim bsp_AppDate As DateTime = Form1.txtAppDate.Text
Dim bsp_Priority = Form1.txtPriority.Text
Dim bsp_Status = Form1.txtStatus.Text

Dim ins As String

Try

ins = "INSERT INTO bsp_ChgReq (bsp_AddedBy, bsp_DateAdded, bsp_ShortDesc " & _
",bsp_ReqBy , bsp_EstBy, bsp_AppBy, bsp_AppDate, bsp_Priority, bsp_Status) " & _
" VALUES" _
& "( " _
& " @bsp_AddedBy " _
& ", @bsp_DateAdded " _
& ", @bsp_ShortDesc " _
& ", @bsp_ReqBy " _
& ", @bsp_EstBy " _
& ", @bsp_AppBy " _
& ", @bsp_AppDate " _
& ", @bsp_Priority " _
& ", @bsp_Status " _
& ")"

Dim mySqlDataTable As DataTable = mySqlDataSet.Tables("bsp_ChgReq")

Dim newRow As DataRow = mySqlDataTable.NewRow()

newRow("bsp_AddedBy") = bsp_AddedBy
newRow("bsp_DateAdded") = Format(bsp_DateAdded, "yyyy-MM-dd 00:00:00.000")
newRow("bsp_ShortDesc") = bsp_ShortDesc
newRow("bsp_ReqBy") = Format(bsp_ReqBy, "yyyy-MM-dd hh:mm:ss.000")
newRow("bsp_EstBy") = Format(bsp_EstBy, "yyyy-MM-dd hh:mm:ss.000")
newRow("bsp_AppBy") = bsp_AppBy
newRow("bsp_AppDate") = Format(bsp_AppDate, "yyyy-MM-dd hh:mm:ss.000")
newRow("bsp_Priority") = bsp_Priority
newRow("bsp_Status") = bsp_Status

mySqlDataTable.Rows.Add(newRow)


Dim inscmd As SqlCommand = New SqlCommand(ins, mySqlConnection)

With inscmd
.Parameters.Add("@bsp_AddedBy", SqlDbType.Char, 8)
.Parameters("@bsp_AddedBy").Value = bsp_AddedBy

.Parameters.Add("@bsp_DateAdded", SqlDbType.DateTime)
.Parameters("@bsp_DateAdded").Value = Format(bsp_DateAdded, "yyyy-MM-dd 00:00:00.000")

.Parameters.Add("@bsp_ShortDesc", SqlDbType.Char, 60)
.Parameters("@bsp_ShortDesc").Value = bsp_ShortDesc

.Parameters.Add("@bsp_ReqBy", SqlDbType.DateTime)
.Parameters("@bsp_ReqBy").Value = Format(bsp_ReqBy, "yyyy-MM-dd 00:00:00.000")

.Parameters.Add("@bsp_EstBy", SqlDbType.DateTime)
.Parameters("@bsp_EstBy").Value = Format(bsp_EstBy, "yyyy-MM-dd 00:00:00.000")

.Parameters.Add("@bsp_AppBy", SqlDbType.Char, 8)
.Parameters("@bsp_AppBy").Value = bsp_AppBy

.Parameters.Add("@bsp_AppDate", SqlDbType.DateTime)
.Parameters("@bsp_AppDate").Value = Format(bsp_AppDate, "yyyy-MM-dd 00:00:00.000")

.Parameters.Add("@bsp_Priority", SqlDbType.Char, 1)
.Parameters("@bsp_Priority").Value = bsp_Priority

.Parameters.Add("@bsp_Status", SqlDbType.Char, 10)
.Parameters("@bsp_Status").Value = bsp_Status
End With

mySqlAdapter.InsertCommand = inscmd
mySqlAdapter.Update(mySqlDataSet, "bsp_ChgReq")

 

[Qik3Coder]

you should be able to get the identity out as a select at the end of your insert, if it isn't a property of the command object itself.

Sometimes, if it's not an identity then you just have to SELECT all over, and rember to set the focus to the row that was just created.

-Sometimes the answer to your question is the hack that works