insert from text box to SQL please help RESOLVED

[d2005]

i have my connections set up to the database
i have a textbox called txt_name
a btn called btn_submit

how do i insert the information in the text box on the button click to my SQL database

[mendhak]

Dim myCommand As New SqlCommand("INSERT INTO tablename(fieldname) VALUES('" & txt_name.Text & "';", myConnectionObject)
    myCommand.Connection.Open()
    myCommand.ExecuteNonQuery()
    myConnection.Close()

[d2005]

i seem to get an error with the execute nonqquery line.
should this code be placed in the button click event.
Thanks

[mendhak]

What's the error? Where did you place the code?

[d2005]

i but the code here
Dim sqlInsertmobile As New SqlCommand("INSERT INTO user_table(mobile) VALUES('" & txt_name.Text & "';", oSQLConn)
sqlInsertmobile.Connection.Open()
sqlInsertmobile.ExecuteNonQuery()
oSQLConn.Close()
in the btn_submit_click

the error is
(title) Line 1: Incorrect syntax near ';'.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ';'.

then
Line 79: Dim sqlInsertmobile As New SqlCommand("INSERT INTO user_table(mobile) VALUES('" & txt_name.Text & "';", oSQLConn)
Line 80: sqlInsertmobile.Connection.Open()
Line 81: sqlInsertmobile.ExecuteNonQuery()
Line 82: oSQLConn.Close()
Line 83: End Sub
line 81 seems to be the problem.


is SQL case sensitive as the mobile column would be Mobile

[mendhak]

Oops, my mistake, that should be:

Dim sqlInsertmobile As New SqlCommand("INSERT INTO user_table(mobile) VALUES('" & txt_name.Text & "');", oSQLConn)

[d2005]

yeah i have adapted to this now,
on tis page i have 2 textboxes to go into a table with 2 columns
mobile and name
when click the button i get an error

Private Sub btn_submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_submit.Click
 
        Dim sqlInsertmob As New SqlCommand("INSERT INTO user_table(Mobile) VALUES('" & txt_mobile_number.Text & "');", oSQLConn)
        Dim sqlInsertname As New SqlCommand("INSERT INTO user_table(Name) VALUES('" & txt_name.Text & "');", oSQLConn)
 
        sqlInsertmob.Connection.Open()
        'sqlInsertname.Connection.Open()
        sqlInsertmob.ExecuteNonQuery()
        sqlInsertname.ExecuteNonQuery()
        oSQLConn.Close()

then i get this error, it seems to be a problem with my table???

Cannot insert the value NULL into column 'Name', table 'Con_test.dbo.user_table'; column does not allow nulls. INSERT fails. The statement has been terminated.

[mendhak]

Step through the code and check whether a value exists in txt_name.Text or whether it is valid at all.

Second, change this:

INSERT INTO user_table(Name) VALUES('

to

INSERT INTO user_table([Name]) VALUES('

Use square brackets for reserved keywords.

[d2005]

i put on the square brackets,
the textbox txt_name is just a box i dragged on, when i click the button it is to insert the details entered into the textboxes mobile number and name.
the error returned is

Cannot insert the value NULL into column 'Name', table 'Con_test.dbo.user_table'; column does not allow nulls. INSERT fails. The statement has been terminated.

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Name', table 'Con_test.dbo.user_table'; column does not allow nulls. INSERT fails. The statement has been terminated.


Line 84: sqlInsertmob.Connection.Open()
Line 85: 'sqlInsertname.Connection.Open()
Line 86: sqlInsertmob.ExecuteNonQuery()
Line 87: sqlInsertname.ExecuteNonQuery()
Line 88: oSQLConn.Close()

[d2005]

THATS THE WHOLE CODE, ANY IDEAS WOULD BE APPRECIATED

Imports System
Imports System.data
Imports System.Data.SqlClient
 
Public Class WebForm1
    Inherits System.Web.UI.Page
 
#Region " Web Form Designer Generated Code "
 
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
 
    End Sub
    Protected WithEvents txt_mobile_number As System.Web.UI.WebControls.TextBox
    Protected WithEvents txt_name As System.Web.UI.WebControls.TextBox
    Protected WithEvents btn_submit As System.Web.UI.WebControls.Button
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
    Protected WithEvents lbl_time As System.Web.UI.WebControls.Label
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Label2 As System.Web.UI.WebControls.Label
 
    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object
 
    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub
 
#End Region
 
    ' declare the data set and data adapters
    'declare connection variable to database
    Private dsUsers As New DataSet
    Private daUsers As New SqlDataAdapter
    Dim oSQLConn As SqlConnection = New SqlConnection
    
   
 
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'call load users
        loadusers()
       
 
 
    End Sub
 
    Private Sub loadusers()
        'connect to database
 
        'Dim oSQLConn As SqlConnection = New SqlConnection
        oSQLConn.ConnectionString = "Data Source=(local);" & _
        "Initial Catalog=Con_Test;" & _
        "Integrated Security=SSPI"
        oSQLConn.Open()
 
 
        'databind the infromation
        'If SqlConnection1.State = ConnectionState.Closed Then SqlConnection1.Open()
      
        daUsers.SelectCommand = New SqlCommand("SELECT * from user_table ORDER BY Name", oSQLConn)
        daUsers.Fill(dsUsers)
 
        'DataGrid()
        DataGrid1.DataSource = dsUsers.Tables(0)
        DataGrid1.DataBind()
        oSQLConn.Close()
        
        
 
    End Sub
 
 
 
 
    
    Private Sub btn_submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_submit.Click
 
 
        Dim sqlInsertname As New SqlCommand("INSERT INTO user_table([Name]) VALUES('" & txt_name.Text & "');", oSQLConn)
        Dim sqlInsertmob As New SqlCommand("INSERT INTO user_table(Mobile) VALUES('" & txt_mobile_number.Text & "');", oSQLConn)
        
        'sqlInsertmob.Connection.Open()
        sqlInsertname.Connection.Open()
        sqlInsertmob.ExecuteNonQuery()
        sqlInsertname.ExecuteNonQuery()
        oSQLConn.Close()
        'Server.Transfer("user_table.aspx")
 
       
 
 
    End Sub
End Class

[mendhak]

Did you step through the code?

[d2005]

yeah buddy i got it workin,
i was using more than one connection and they must cancel each other out, the trick is to use one line of code for multiple inserts,

Dim sqlInsertuser As New SqlCommand("INSERT INTO tb_user(c_user_id,c_orig_addr, VALUES('" & txt_mobile_number.Text & "','" & txt_name.Text & "');", oSQLConn)
sqlInsertuser.Connection.Open()
sqlInsertuser.ExecuteNonQuery()
oSQLConn.Close()