I am trying to create a form, and then insert the values entered by a user into a sql database, I have enclosed the page code below. Everything works except the data is not being inserted into the database, and i keep getting the default message in my error message section. I took this right from the quick start tutorial and started working with it, and keep getting an error.
I believe the error is located in the INSERT statement
Put a more specific error message in while debugging:
<%@. Page Language="vb" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %
<html
<script language="VB" runat="server"
Dim MyConnection As SqlConnectionSub Page_Load(Sender As Object, E As EventArgs)
MyConnection = New SqlConnection("server=localhost;database=planetauction;uid=planetauction;pwd=bean13")
If Not (IsPostBack)
BindGrid()
Page.DataBind()
End If
End SubSub AddAuthor_Click(Sender As Object, E As EventArgs)
Page.Validate()
If Not Page.IsValid
Return
End IfDim DS As DataSet
Dim MyCommand As SqlCommandIf txtLastName.Value = ""
Message.InnerHtml = "ERROR: Null values not allowed for Author ID, " & _
"Name or Phone"
Message.Style("color") = "red"
BindGrid()
Return
End IfDim InsertCmd As String = "insert into users (txtLastName) values (@.lastname)"
MyCommand = New SqlCommand(InsertCmd, MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@.lastname", SqlDbType.NVarChar, 50))
MyCommand.Parameters("@.lastname").Value = txtLastName.ValueMyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the " & _
"same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
"the fields are correctly filled out"
End If
Message.Style("color") = "red"End Try
MyCommand.Connection.Close()
BindGrid()
End SubSub BindGrid()
Dim MyCommand As SqlDataAdapter = new SqlDataAdapter( _
"select * from users", MyConnection)Dim DS As DataSet = new DataSet()
MyCommand.Fill(DS, "Users")MyDataGrid.DataSource=DS.Tables("Users").DefaultView
MyDataGrid.DataBind()
End Sub</script
<body style="font: 10pt verdana"
<form runat="server" ID="Form1"
<h3><font face="Verdana">Inserting a Row of Data</font></h3
<table width="95%">
<tr>
<td valign="top"
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/
</td>
<td valign="top"
<table style="font: 8pt verdana">
<tr>
<td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">Add a New Author:</td>
</tr>
<tr>
<td nowrap>Last Name: </td>
<td>
<input type="text" id="txtLastName" runat="server" NAME="txtLastName"><br
</td>
</tr>
<tr>
<td></td>
<td style="padding-top:15">
<input type="submit" OnServerClick="AddAuthor_Click" value="Add Author" runat="server" ID="Submit1" NAME="Submit1">
</td>
</tr>
<tr>
<td colspan="2" style="padding-top:15" align="center">
<span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
</td>
</tr>
</table
</td>
</tr>
</table
</form
</body>
</html>
|||Mr. Reily:
Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the " & _
"same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
"the fields are correctly filled out. Exception: " & Exp.ToString()
End IfMessage.Style("color") = "red"
End Try
Thanks for your advice. I added the detailed error message and here is what I got:
ERROR: Could not add record, please ensure the fields are correctly filled out. Exception: System.Data.SqlClient.SqlException: Invalid column name 'txtLastName'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.reference3_aspx.AddAuthor_Click(Object Sender, EventArgs E) in http://localhost/planetauction/reference3.aspx:line 47
I really think the error is in the insert statement. I need to put either double or single quotes around something in the insert statement, right now the database believes the value is referring to a column name, rather than inserting that data into the column specified.
Let me know what u think.
Thanks|||Thanks guys, got it all figured out.
It was the insert statment that was screwed up. For anyone else with the same problem, I simply mixed up the values, and the column names in the sql insert statements.
For Reference:
Dim InsertCmd As String = "insert into users (COLUMNNAME1, COLUMNNAME2) values (@.FIRST SQL PARAMETER LISTED BELOW, SECOND SQL PARAMETER LISTED BELOW, etc)"
MyCommand = New SqlCommand(InsertCmd, MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@.lastname", SqlDbType.NVarChar, 50))
MyCommand.Parameters("@.lastname").Value = txtLastName.ValueMyCommand.Connection.Open()
Kevin Yu - MSFT: