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")

 

[johnwm]

This forum is targeted at VB6 - try forum796

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints