none
How can I send row data to an SQL Database table via VB 2008 code and permanently save and display the result? RRS feed

  • Question

  •  

    Note: This is related to:

    How can I make some graphics drawings stick while others disappear?

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1

     

     

    Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called “HexMap” that contains some columns that I am ready to insert some row data into.  Here is what my program should do:

     

    As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor.  If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database.  Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action:

     

    http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg

     -sorry, I didn't capture the mouse cursor inside the red hexagon

     

    I think I am missing something since I appear to be able to connect successfully to the database table.  Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer).  I am hoping someone will review my code snippet (below) and tell me what I am missing.  What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected.      

     

    Code Snippet

    '======================================================================================

    Dim CN As New SqlClient.SqlConnection()

    Dim da As New SqlClient.SqlDataAdapter

     

    'Consider using Me._adapter that is used already

     

    CN.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    CN.Open()

     

    'Use the following code to verify that a connection to the database has achieved

    If CN.State = ConnectionState.Open Then

     MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")

    End If

     

    Try

     

    Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)

    End Try

     

    'use the Connection object to execute statements

    'against the database and then close the connection

    da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)

     

    If CN.State = ConnectionState.Open Then CN.Close()

    '==========================================================================

     

    Dim rows As Integer

     

    rows = 0

     

    Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN)

     

    CN.Open()

     

    rows = CMD.ExecuteNonQuery

     

    If rows = 1 Then

                    MsgBox("Table HexMap updated successfully")

                Else

                    MsgBox("Failed to update the HexMap table")

    End If

     

    If CN.State = ConnectionState.Open Then CN.Close()

    '==========================================================================

     

     

     

     

    Thanks for reviewing my code.

     

    Technozoide 

    Sunday, March 16, 2008 3:44 PM

All replies

  • Problem No. 1: You are missing "Into" from your insert command text.
    Problem No. 2: Value of columng "Territory" equal to "Territory_ComboBox1.Text" You need to use parametrized query in order to insert values dynamically.
    Sunday, March 16, 2008 6:58 PM
  • Giorgi:

     

    As far as inserting an "Into" in my Insert command, here is the generic format I am using:

     

    INSERT table_name (column_names) VALUES (values)

     

    where column_names and values are comma-separated lists of columns and their respective values. From what I understand, using INTO refers to using all columns, which I do not want to do here.  The list of column names you see are only a subset of all columns in my table.  The INSERT statement inserts new rows in a table, and supposedly I do not have to specify values for all columns in the table, but the values list must contain as many items as there are column names in the first list.  So this is how I am using the INSERT command.

     

    I still tried your suggestion, and I was not able to get it to work by adding "Into", but it appeared to execute fine without the "Into".

     

    As far as the "Territory" issue, unless you have some code I can try, I went ahead and removed this potential problem by simply typing in actual values to see if I can at least write anything to my database.

     

    Again, this database appears to be made of solid steel, cause I cannot write anything to it, despite all indications that my connection is fine and the commands are executing.

     

    Here is the current code:

     

    Code Snippet

    Dim CN As New SqlClient.SqlConnection()

    Dim da As New SqlClient.SqlDataAdapter 'Consider using Me._adapter that is used already

     

    CN.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Gameboard1.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    CN.Open()

     

    'Use the following code to verify that a connection to the database has achieved

    If CN.State = ConnectionState.Open Then

    MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")

    End If

     

    Try

    Catch ex As Exception

    MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)

    End Try

     

    'use the Connection object to execute statements

    'against the database and then close the connection

    da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)

    '===================================================================================

    Dim rows As Integer

    rows = 0

    Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES (1, 2,3,4,5,'Texas')", CN)

     

    rows = CMD.ExecuteNonQuery

    If rows = 1 Then

    MsgBox("Table HexMap updated successfully")

    Else

    MsgBox("Failed to update the HexMap table")

    End If

     

    '======================================================================================

    If CN.State = ConnectionState.Open Then CN.Close()

     

     

    By the way, how do you keep an eye on a database as your program is running to see if your database is changing?  I currently go to the Server Explorer and Show Table Data

     

    Let me know if there is a better way.  Thanks again Giorgi!

     

    Techno

    Sunday, March 16, 2008 9:17 PM