Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Having trouble inserting into SQL table

Status
Not open for further replies.
sjdk

sjdk

Programmer
May 2, 2003
59
0
0
US
First, I am a newbie to visual studio, so forgive me if I ask basic questions...

I am trying to create a simple form with 3 text boxes on it. I want to be able to enter data into the 3 boxes and then insert the new record to the SQL table. I have a button to click to save the data. Seems simple enough...

The error I get when I click my save button is "input string was not in correct format"

Here is my code..

Code: 
Public Class Category
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents SqlDataAdapter2 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents DsCategoryDesc1 As VDIR_2003.dsCategoryDesc
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents Categoryno As System.Windows.Forms.TextBox
    Friend WithEvents CategoryDesc As System.Windows.Forms.TextBox
    Friend WithEvents CategorySort As System.Windows.Forms.TextBox
    Friend WithEvents Button1 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DsCategoryDesc1 = New VDIR_2003.dsCategoryDesc
        Me.SqlDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
        Me.Categoryno = New System.Windows.Forms.TextBox
        Me.CategoryDesc = New System.Windows.Forms.TextBox
        Me.CategorySort = New System.Windows.Forms.TextBox
        Me.Button1 = New System.Windows.Forms.Button
        CType(Me.DsCategoryDesc1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DsCategoryDesc1
        '
        Me.DsCategoryDesc1.DataSetName = "dsCategoryDesc"
        Me.DsCategoryDesc1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'SqlDataAdapter2
        '
        Me.SqlDataAdapter2.DeleteCommand = Me.SqlDeleteCommand1
        Me.SqlDataAdapter2.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter2.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter2.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblCategoryDesc", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("CategoryNo", "CategoryNo"), New System.Data.Common.DataColumnMapping("CategoryDesc", "CategoryDesc"), New System.Data.Common.DataColumnMapping("Sort", "Sort")})})
        Me.SqlDataAdapter2.UpdateCommand = Me.SqlUpdateCommand1
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM tblCategoryDesc WHERE (CategoryNo = @Original_CategoryNo) AND (Catego" & _
        "ryDesc = @Original_CategoryDesc OR @Original_CategoryDesc IS NULL AND CategoryDe" & _
        "sc IS NULL) AND (Sort = @Original_Sort OR @Original_Sort IS NULL AND Sort IS NUL" & _
        "L)"
        Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CategoryNo", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CategoryNo", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CategoryDesc", System.Data.SqlDbType.NVarChar, 500, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CategoryDesc", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Sort", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sort", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=B04XPDSKENDALL;packet size=4096;integrated security=SSPI;data sour" & _
        "ce=BL4DEVELOPMENT;persist security info=False;initial catalog=testNewVDIR"
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO tblCategoryDesc(CategoryNo, CategoryDesc, Sort) VALUES (@CategoryNo, " & _
        "@CategoryDesc, @Sort); SELECT CategoryNo, CategoryDesc, Sort FROM tblCategoryDes" & _
        "c WHERE (CategoryNo = @CategoryNo) ORDER BY Sort"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryNo", System.Data.SqlDbType.Int, 4))
        Me.SqlInsertCommand1.Parameters("@CategoryNo").Value = Categoryno.Text
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryDesc", System.Data.SqlDbType.NVarChar, 500))
        Me.SqlInsertCommand1.Parameters("@CategoryDesc").Value = CategoryDesc.Text
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Sort", System.Data.SqlDbType.Int, 4))
        Me.SqlInsertCommand1.Parameters("@Sort").Value = CategorySort.Text
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT CategoryNo, CategoryDesc, Sort FROM tblCategoryDesc ORDER BY Sort"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE tblCategoryDesc SET CategoryNo = @CategoryNo, CategoryDesc = @CategoryDesc" & _
        ", Sort = @Sort WHERE (CategoryNo = @Original_CategoryNo) AND (CategoryDesc = @Or" & _
        "iginal_CategoryDesc OR @Original_CategoryDesc IS NULL AND CategoryDesc IS NULL) " & _
        "AND (Sort = @Original_Sort OR @Original_Sort IS NULL AND Sort IS NULL); SELECT C" & _
        "ategoryNo, CategoryDesc, Sort FROM tblCategoryDesc WHERE (CategoryNo = @Category" & _
        "No) ORDER BY Sort"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryNo", System.Data.SqlDbType.Int, 4, "CategoryNo"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryDesc", System.Data.SqlDbType.NVarChar, 500, "CategoryDesc"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Sort", System.Data.SqlDbType.Int, 4, "Sort"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CategoryNo", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CategoryNo", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CategoryDesc", System.Data.SqlDbType.NVarChar, 500, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CategoryDesc", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Sort", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Sort", System.Data.DataRowVersion.Original, Nothing))
        '
        'Categoryno
        '
        Me.Categoryno.Location = New System.Drawing.Point(16, 32)
        Me.Categoryno.Name = "Categoryno"
        Me.Categoryno.TabIndex = 0
        Me.Categoryno.Text = "TextBox1"
        '
        'CategoryDesc
        '
        Me.CategoryDesc.Location = New System.Drawing.Point(128, 32)
        Me.CategoryDesc.Name = "CategoryDesc"
        Me.CategoryDesc.Size = New System.Drawing.Size(504, 20)
        Me.CategoryDesc.TabIndex = 1
        Me.CategoryDesc.Text = "TextBox2"
        '
        'CategorySort
        '
        Me.CategorySort.Location = New System.Drawing.Point(640, 32)
        Me.CategorySort.Name = "CategorySort"
        Me.CategorySort.TabIndex = 2
        Me.CategorySort.Text = "TextBox3"
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(512, 160)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 3
        Me.Button1.Text = "Button1"
        '
        'Category
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(752, 326)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.CategorySort)
        Me.Controls.Add(Me.CategoryDesc)
        Me.Controls.Add(Me.Categoryno)
        Me.Name = "Category"
        Me.Text = "Category"
        CType(Me.DsCategoryDesc1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region


    Private Sub Category_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load



    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Me.SqlConnection1.Open()
        Me.SqlInsertCommand1.ExecuteNonQuery()
        Me.SqlConnection1.Close()

    End Sub
End Class

Thanks in advance for any suggestions/assistance!!!
 
Sort by date Sort by votes
You'd do well to stop using the wizard. I don't feel like reading all that "code" that it spits out, and it is often sub-optimal. As you're finding now, using the wizard becomes a crutch that prevents you from *really* learning what goes on in a program as well.

My guess is, you need to reset your parameter values when you have entered the text boxes (and if you don't, then they remain "null" or String.Empty that is set when the form is initialized).

I'm not sure, but this is where I would look first.

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page
 
Upvote 0 Downvote
Many thanks for your advise! I think I will take your suggestion and skip the wizards. I thought I was saving time, but I can see that is really not the case!

Have a great day!
 
Upvote 0 Downvote
Yes, they will cause you much pain in the long run.

If you run into any problems, you know a good place to come for advices!

Here is an example (the first project I could find was one using an access database, but this will give you the idea).

Code: 
Dim cnn As New OleDbConnection(_connStr) 
Dim com As New OleDbCommand() 
com.Connection = cnn 

com.CommandText = "insert into Interruptions (Int_Date, Interrupted, Client, Interruptor, Int_Notes) values (" + "#" + dpIntDate.Text + "#,'" + interrupted + "','" + txtClient.Text + "','" + txtInterruptor.Text + "','" + rtbNotes.Text + "')" 

cnn.Open() 

com.ExecuteNonQuery()

com.Dispose() 
cnn.Close() 
cnn.Dispose()

I do encourage you to keep using parameters though (and look into stored procedures on database side). Once you have your command object created, you can add parameters using the Command.Parameters.Add() method.

I took out all the try/catch stuff, because I'm not 100% sure how to write it in VB and didn't want to steer you wrong. But that would be good to have too (so that if the database is down your app does not crash).

In this case the connection string is stored in a private variable, but there are other ways to do that as well (I like using the .config files for larger projects).

Hope this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

2009luca
  • Locked
  • Question
TWO table with the same classname 1
Replies
3
Views
89
Andrzejek
Andrzejek
Niki_S
  • Locked
  • Question
How to insert excel sheet into sql server using vb 6?
Replies
6
Views
99
Andrzejek
Andrzejek
Niki_S
  • Locked
  • Question
How to get data from sql into a combo box in vb6?
Replies
3
Views
86
Andrzejek
Andrzejek
sal21
  • Locked
  • Question
get data from iframe and table
Replies
0
Views
33
sal21
sal21
sal21
  • Locked
  • Question
WHY! ie dont show the table of result 1
Replies
8
Views
72
sal21
sal21

Part and Inventory Search

Sponsor

Back
Top