INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQLDataAdapter Update problem

SQLDataAdapter Update problem

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close