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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Input string error in "update statment" 1

Status
Not open for further replies.
toddw607

toddw607

Programmer
Apr 2, 2007
55
US
Hello,
I have a problem with updating a datagrid. I have a function for a save button but when I press it it gives me an error saying that my update statement is incorrect. Here is the code to my update statement:
Private Sub doSave(ByVal sender As Object, ByVal e As System.EventArgs)

Dim myCommand As SqlCommand
Dim dstCopy As New DataSet
Dim strQuery As String

objConnection = New SqlConnection("server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")
objConnection.Open()
Dim dgItem As DataGridItem
For Each dgItem In myInfo.Items
Dim txtCAGE As TextBox = _
CType(dgItem.Cells(0).FindControl("txtCAGE"), TextBox)

Dim txtName As TextBox = _
CType(dgItem.Cells(1).FindControl("txtName"), TextBox)

Dim txtStreet As TextBox = _
CType(dgItem.Cells(2).FindControl("txtStreet"), TextBox)

Dim txtCity As TextBox = _
CType(dgItem.Cells(3).FindControl("txtCity"), TextBox)

Dim txtState As TextBox = _
CType(dgItem.Cells(4).FindControl("txtState"), TextBox)

Dim txtNation As TextBox = _
CType(dgItem.Cells(5).FindControl("txtNation"), TextBox)

Dim txtPostal As TextBox = _
CType(dgItem.Cells(6).FindControl("txtPostal"), TextBox)

Dim txtPhone As TextBox = _
CType(dgItem.Cells(7).FindControl("txtPhone"), TextBox)



strQuery = "INSERT INTO XH (CAGE, Name, Street, City, State, Nation, Postal, Phone) VALUES('" & +txtCAGE.Text + _
+ ", " + txtName.Text + ", " + txtStreet.Text + ", " + txtCity.Text + ", " + txtState.Text + ", " + & _
+txtNation.Text + ", " + txtPostal.Text + ", " + txtPhone.Text + ")"

myCommand = New SqlCommand(strQuery, objConnection)
myCommand.ExecuteNonQuery()

Next
objConnection.Close()
ShowDataGrid()
End Sub


and here is my datagrid:

<form id="Form1" runat="server">
<H3 id="H3_1">Editing XH from SQL Server </H3>

<asp:DataGrid id="myInfo"

OnItemCommand="doInsert"
OnDeleteCommand="DBDelDataGrid_Delete"
ShowFooter="True"
runat="server"
DataKeyField="id"
AutoGenerateColumns="False" CellPadding="4"
BackColor=" #C6DEFF"
EditItemStyle-BackColor="#eeeeee"
HeaderStyle-BackColor="Black"
HeaderStyle-ForeColor="Blue"
HeaderStyle-HorizontalAlign="Center"
HeaderStyle-Font-Bold="True">
<Columns>
<asp:TemplateColumn HeaderText="CAGE">
<FooterTemplate>
<asp:TextBox ID="add_CAGE" Columns="7" Runat="Server" />
</FooterTemplate>

<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtCAGE" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("CAGE") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="CAGE" Columns="7"
Text='<%# Container.DataItem("CAGE") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Name">
<FooterTemplate>
<asp:TextBox ID="add_Name" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtName" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("Name") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Name" Columns="7"
Text='<%# Container.DataItem("Name") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Street">
<FooterTemplate>
<asp:TextBox ID="add_Street" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtStreet" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("Street") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Street" Columns="7"
Text='<%# Container.DataItem("Street") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="City">
<FooterTemplate>
<asp:TextBox ID="add_City" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtCity" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("City") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="City" Columns="7"
Text='<%# Container.DataItem("City") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="State">
<FooterTemplate>
<asp:TextBox ID="add_State" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtState" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("State") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="State" Columns="7"
Text='<%# Container.DataItem("State") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Nation">
<FooterTemplate>
<asp:TextBox ID="add_Nation" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtNation" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("Nation") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Nation" Columns="7"
Text='<%# Container.DataItem("Nation") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Postal">
<FooterTemplate>
<asp:TextBox ID="add_Postal" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtPostal" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("Postal") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Postal" Columns="7"
Text='<%# Container.DataItem("Postal") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Phone">
<FooterTemplate>
<asp:TextBox ID="add_Phone" Columns="7" Runat="Server" />
</FooterTemplate>
<ItemTemplate>
<asp:TextBox MaxLength="50" runat="server" ID="txtPhone" cssclass="TEXTBOX_MEDIUM_DG1" Text='<%# Container.DataItem("Phone") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Phone" Columns="7"
Text='<%# Container.DataItem("Phone") %>' Runat="server" />
</EditItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Insert">
<FooterTemplate>
<asp:Button CommandName="Insert" Text="Add" ID="btnAdd" Runat="server" />
</FooterTemplate>
<ItemTemplate>
<asp:Button CommandName="Delete" Text="Delete" ID="btnDel" Runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Save">
<FooterTemplate>
<asp:Button CommandName="Save" Text="Save" ID="btnSave" OnClick="doSave" runat="server" />
</FooterTemplate>
</asp:TemplateColumn>

</Columns>
</asp:DataGrid>



</form>
</body>
</html>

Can someone please tell me what is the problem. I've been working on this for almost a week and I'm not sure which syntax to use. Thank you.

 
Sort by date Sort by votes
todd - I do not work specifically with SQL - are the plus signs needed? My recollection of the format is:
Code: 
INSERT INTO IntCC ([Fname], [Email], [Country], [OrderNo], [Price], [Comments]) VALUES (" & varFname & ", " & varEmail & ", " & varCountry & "," & varOrderno & ", " & varPrice & ", " & varComments & ")"
 
Upvote 0 Downvote
The problem is the way you are constructing your SQL statement. You can either debug that portion, or you would be better off creating a stored procedure with parameters. I would be easier to debug, be more secure, be quicker and easier to maintain.

Jim
 
Upvote 0 Downvote
I was interested in creating a stored procedure, it seems as if this would cause much less problems in my code, aside from cleaning it up, but I've never done that before. I have the basic idea but I'm not sure about the format. Can you explain? Thank you both for your responses.
 
Upvote 0 Downvote
You can take a look in Books On Line(BOL). It should be installed on your PC. That is the help for sQL 2000.
Basically:
Code: 
Create Procedure MyProcedure
     @param1 datatype,
     @param2 datatype ..etc
AS

BEGIN
   Insert Into YourTable(col1, col2 ..etc)
               Values(@param1, @param2 ...etc)
END
 
Upvote 0 Downvote
Thanks alot Jim! I'll try that, it seems much easier than what I've been doing. I will let you know how I come along.
 
Upvote 0 Downvote
Sorry to both you again but I'm getting a strange error and I've tried alot of debugging and I still cannot find the error. Here is the error I'm getting:
An SqlParameter with ParameterName 'BusName' is not contained by this SqlParameterCollection.


I'm using this simple code:
<%@import namespace="system.data.SQLClient"%>
<%@import namespace="system.data"%>

<SCRIPT LANGUAGE="vb" Runat="server">
Sub Add(sender as object, e as eventargs)
If Page.IsValid()
Dim dbCommand As SqlCommand
Dim DataConn as SQLConnection
DataConn = New SqlConnection("server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")

'for output from the stored procedure
Dim workParam As SQLParameter = Nothing

'call to store procedure which will be executed in a moment
dbCommand = New SqlCommand("XHInsert", DataConn)

'our command type is for a store procedure.
'Not a string SQL statement
dbCommand.CommandType = CommandType.StoredProcedure

'We are inserting from a textbox named fname
'which will reference a
'variable @fname in the stored proc
dbCommand.Parameters.Add(New SqlParameter("@CAGE", SqlDbType.NVarChar, 5))
dbCommand.Parameters("@CAGE").Value = CAGE.Text.Trim()

'We are inserting from a textbox named lname which will reference
'variable @lname in the stored proc
dbCommand.Parameters.Add(New SqlParameter("@BusName", SqlDbType.NVarChar, 25))
dbCommand.Parameters("BusName").Value = BusName.Text.Trim()


dbCommand.Parameters.Add(New SqlParameter("@Street", SqlDbType.NVarChar, 25))
dbCommand.Parameters("Street").Value = Street.Text.Trim()

dbCommand.Parameters.Add(New SqlParameter("@City", SqlDbType.NVarChar, 20))
dbCommand.Parameters("City").Value = City.Text.Trim()

dbCommand.Parameters.Add(New SqlParameter("@State", SqlDbType.NVarChar, 2))
dbCommand.Parameters("State").Value = State.Text.Trim()

dbCommand.Parameters.Add(New SqlParameter("@Nation", SqlDbType.NVarChar, 20))
dbCommand.Parameters("Nation").Value = Nation.Text.Trim()

dbCommand.Parameters.Add(New SqlParameter("@Postal", SqlDbType.NVarChar, 10))
dbCommand.Parameters("Postal").Value = Postal.Text.Trim()

dbCommand.Parameters.Add(New SqlParameter("@Phone", SqlDbType.NVarChar, 12))
dbCommand.Parameters("Phone").Value = Phone.Text.Trim()
'Establish an output parameter to return a value from the stored procedure

'Our parameter is for output as mentioned
workParam.Direction = ParameterDirection.Output

'open connection and execute. This is not a query so ExecuteNonQuery is used
DataConn.open()
dbCommand.ExecuteNonQuery

'declare variable NewID as set it to @ID which is our output varaible
'in the stored proc. In this case @ID is identity of the new record
'inserted (@@identity)
'dim NewID as Integer = dbCommand.Parameters("@ID").Value

'close connection
DataConn.close

'set the session of Listing to the NewID
'Session("Login") = NewID
End If
End Sub
</SCRIPT>


<FORM id="FORM1" runat="server">
CAGE: <asp:textbox id="CAGE" runat="server"/>

BusName: <asp:textbox id="BusName" runat="server" />

Street: <asp:textbox id="Street" runat="server"/>

City: <asp:textbox id="City" runat="server"/>

State: <asp:textbox id="State" runat="server"/>

Nation: <asp:textbox id="Nation" runat="server"/>

Postal: <asp:textbox id="Postal" runat="server"/>

Phone: <asp:textbox id="Phone" runat="server"/>

<asp:button runat="server" onclick="Add" text="Add"/>
</FORM>


and here is my stored procedure:
CREATE Procedure XHinsert
@CAGE nvarchar (5),
@BusName nvarchar (25),
@Street nvarchar (25),
@City nvarchar (20),
@State nvarchar (2),
@Nation nvarchar (20),
@Postal nvarchar (10),
@Phone nvarchar (12)
AS

BEGIN
Insert Into XH(CAGE, BusName, Street, City, State, Nation, Postal, Phone)
Values(@CAGE, @BusName, @Street, @City, @State, @Nation, @Postal, @Phone)
END
GO


What could I be doing wrong?
 
Upvote 0 Downvote
Make sure the all your params have an"@" in front of them:
Code: 
dbCommand.Parameters("[b]@[/b]BusName").Value = BusName.Text.Trim()
 
Upvote 0 Downvote
AARRRRGGH :) I fixed the previous error,
Now I'm getting another error:
Object reference not set to an instance of an object.
 
Upvote 0 Downvote
The error is occurring in this line:
workParam.Direction = ParameterDirection.Output

I've also taken out some of the comments so it can be clearer on this screen:
<%@import namespace="system.data.SQLClient"%>
<%@import namespace="system.data"%>
<%@ Page Language="vb" Debug="true" %>

<SCRIPT LANGUAGE="vb" Runat="server" DeBug="True">
Sub Add(sender as object, e as eventargs)
If Page.IsValid()
Dim dbCommand As SqlCommand
Dim DataConn as SQLConnection
DataConn = New SqlConnection("server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")

Dim workParam As SQLParameter = Nothing

dbCommand = New SqlCommand("XHInsert", DataConn)

dbCommand.CommandType = CommandType.StoredProcedure


dbCommand.Parameters.Add(New SqlParameter("@CAGE", SqlDbType.NVarChar, 5))
dbCommand.Parameters("@CAGE").Value = CAGE.Text.Trim()
dbCommand.Parameters.Add(New SqlParameter("@BusName", SqlDbType.NVarChar, 25))
dbCommand.Parameters("@BusName").Value = BusName.Text.Trim()


dbCommand.Parameters.Add(New SqlParameter("@Street", SqlDbType.NVarChar, 25))
dbCommand.Parameters("@Street").Value = Street.Text.Trim()

dbCommand.Parameters.Add(New SqlParameter("@City", SqlDbType.NVarChar, 20))
dbCommand.Parameters("@City").Value = City.Text.Trim()

dbCommand.Parameters.Add(New SqlParameter("@State", SqlDbType.NVarChar, 2))
dbCommand.Parameters("@State").Value = State.Text.Trim()

dbCommand.Parameters.Add(New SqlParameter("@Nation", SqlDbType.NVarChar, 20))
dbCommand.Parameters("@Nation").Value = Nation.Text.Trim()

dbCommand.Parameters.Add(New SqlParameter("@Postal", SqlDbType.NVarChar, 10))
dbCommand.Parameters("@Postal").Value = Postal.Text.Trim()

dbCommand.Parameters.Add(New SqlParameter("@Phone", SqlDbType.NVarChar, 12))
dbCommand.Parameters("@Phone").Value = Phone.Text.Trim()

workParam.Direction = ParameterDirection.Output

DataConn.open()
dbCommand.ExecuteNonQuery

'declare variable NewID as set it to @ID which is our output varaible
'in the stored proc. In this case @ID is identity of the new record
'inserted (@@identity)
'dim NewID as Integer = dbCommand.Parameters("@ID").Value

DataConn.close

'set the session of Listing to the NewID
'Session("Login") = NewID
End If
End Sub
</SCRIPT>


<FORM id="FORM1" runat="server">
CAGE: <asp:textbox id="CAGE" runat="server"/>

BusName: <asp:textbox id="BusName" runat="server" />

Street: <asp:textbox id="Street" runat="server"/>

City: <asp:textbox id="City" runat="server"/>

State: <asp:textbox id="State" runat="server"/>

Nation: <asp:textbox id="Nation" runat="server"/>

Postal: <asp:textbox id="Postal" runat="server"/>

Phone: <asp:textbox id="Phone" runat="server"/>

<asp:button runat="server" onclick="Add" text="Add"/>
</FORM>

 
Upvote 0 Downvote
could you give me an example or someplace to look for one to teach me how to accomplish this task?
 
Upvote 0 Downvote
Sorry to keep bothering you but I changed my code to be:


<%@import namespace="system.data.SQLClient"%>
<%@import namespace="system.data"%>
<%@ Page Language="vb" Debug="true" %>

<SCRIPT LANGUAGE="vb" Runat="server" DeBug="True">
Sub Add(sender as object, e as eventargs)
If Page.IsValid()
Dim dbCommand As SqlCommand
Dim DataConn As SqlConnection
DataConn = New SqlConnection("server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")

'Dim workParam As SqlParameter = Nothing

dbCommand = New SqlCommand("XHInsert", DataConn)

dbCommand.CommandType = CommandType.StoredProcedure

Dim CAGEParam As New SqlParameter("@CAGE", SqlDbType.NVarChar, 5)
CAGEParam.Direction = ParameterDirection.Input
dbCommand.Parameters.Add(CAGEParam)

Dim BusNameParam As New SqlParameter("@BusName", SqlDbType.NVarChar, 25)
BusNameParam.Direction = ParameterDirection.Output
dbCommand.Parameters.Add(BusNameParam)

Dim StreetParam As New SqlParameter("@Street", SqlDbType.NVarChar, 25)
StreetParam.Direction = ParameterDirection.Output
dbCommand.Parameters.Add(StreetParam)

Dim CityParam As New SqlParameter("@City", SqlDbType.NVarChar, 20)
CityParam.Direction = ParameterDirection.Output
dbCommand.Parameters.Add(CityParam)

Dim StateParam As New SqlParameter("@State", SqlDbType.NVarChar, 2)
StateParam.Direction = ParameterDirection.Output
dbCommand.Parameters.Add(StateParam)

Dim NationParam As New SqlParameter("@Nation", SqlDbType.NVarChar, 20)
NationParam.Direction = ParameterDirection.Output
dbCommand.Parameters.Add(NationParam)

Dim PostalParam As New SqlParameter("@Postal", SqlDbType.NVarChar, 10)
PostalParam.Direction = ParameterDirection.Output
dbCommand.Parameters.Add(PostalParam)

Dim PhoneParam As New SqlParameter("@Phone", SqlDbType.NVarChar, 12)
PhoneParam.Direction = ParameterDirection.Output
dbCommand.Parameters.Add(PhoneParam)


DataConn.Open()
dbCommand.ExecuteNonQuery


DataConn.close

End If
End Sub
</SCRIPT>


<FORM id="FORM1" runat="server">
CAGE: <asp:textbox id="CAGE" runat="server"/>

BusName: <asp:textbox id="BusName" runat="server" />

Street: <asp:textbox id="Street" runat="server"/>

City: <asp:textbox id="City" runat="server"/>

State: <asp:textbox id="State" runat="server"/>

Nation: <asp:textbox id="Nation" runat="server"/>

Postal: <asp:textbox id="Postal" runat="server"/>

Phone: <asp:textbox id="Phone" runat="server"/>

<asp:button runat="server" onclick="Add" text="Add"/>
</FORM>

And using the same stored procedure from earlier I am getting the error:

Procedure 'XHinsert' expects parameter '@CAGE', which was not supplied.

It's only selecting this one because it's first, I think the same problem is with all textboxes. Any thoughts on what I could change? I'm sorry I'm new to stored procedures. Thank you.
 
Upvote 0 Downvote
Try using SQL Profiler and have a look at what you are actually executing on the database. This will show you what format your parameter's are in and should highlight any problems.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.
 
Upvote 0 Downvote
Thanks for letting me know about that, I've never used that before. The trace that I got when running my code was:
declare @P1 nvarchar(25)
set @P1=NULL
declare @P2 nvarchar(25)
set @P2=NULL
declare @P3 nvarchar(20)
set @P3=NULL
declare @P4 nvarchar(2)
set @P4=NULL
declare @P5 nvarchar(20)
set @P5=NULL
declare @P6 nvarchar(10)
set @P6=NULL
declare @P7 nvarchar(12)
set @P7=NULL
exec XHInsert @CAGE = default, @BusName = @P1 output, @Street = @P2 output, @City = @P3 output, @State = @P4 output, @Nation = @P5 output, @Postal = @P6 output, @Phone = @P7 output
select @P1, @P2, @P3, @P4, @P5, @P6, @P7


Any thoughts?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JohnB33
  • Locked
  • Question
int type causes error
Replies
0
Views
330
JohnB33
JohnB33
a6green
  • Locked
  • Question
How to configure AT&T / Avaya Definity G3 incoming ISDN trunk
Replies
2
Views
410
a6green
a6green
WIREMESH
  • Locked
  • Question
Play mp4 files in asp.net 2.0 webforms application
Replies
0
Views
112
WIREMESH
WIREMESH
WIREMESH
  • Locked
  • Question
ASP.NET 2.0 Webform compilation error
Replies
1
Views
140
jbenson001
jbenson001
Rajesh Karunakaran
  • Locked
  • Question
How do add Code Behind to a bootstrap input button in ASP.NET C#
Replies
2
Views
217
Rajesh Karunakaran
Rajesh Karunakaran

Part and Inventory Search

Sponsor

Back
Top