InsertParameters

[MC]

Hi all

Complete newby trying to understand something

I have made my own form with some fields and then dragged a SqlDataSource
onto the page and made an Insert to go with the SqlDataSource.

I have a button which calls this

Protected Sub InsertTextBox_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles InsertTextBox.Click

Me.SqlDataSource1.InsertParameters.Add("@CompanyID ",
Me.CompanyIDTextBox.Text)

Me.SqlDataSource1.InsertParameters.Add("@ContactNa me",
Me.ContactNameTextBox.Text)

Me.SqlDataSource1.InsertParameters.Add("@CompanyNa me",
Me.CompanyNameTextBox.Text)

Me.SqlDataSource1.InsertParameters.Add("@Postcode" , Me.PostCodeTextBox.Text)

Try

Me.SqlDataSource1.Insert()

Catch ex As Exception

Response.Write(ex.Message)

End Try

End Sub

I have checked parameter names etc put it wont put the values to the
parameters. Obviously the .Add is wrong, what should be using?

Cheers

[Andrew Morton]

MC wrote:

Me.SqlDataSource1.InsertParameters.Add("@Postcode" ,
Me.PostCodeTextBox.Text) I have checked parameter names etc put it wont put the values to the
parameters. Obviously the .Add is wrong, what should be using?

Add is correct, but it doesn't want to tell you it needs to know the data
type (e.g. SqlDbType.NVarChar) and, for strings, the length.

Andrew

[MC]

Thanks Andrew,

So whats the syntax?

MC

"Andrew Morton" <ak*@in-press.co.uk.invalid> wrote in message
news:eI**************@TK2MSFTNGP09.phx.gbl...

MC wrote:

Me.SqlDataSource1.InsertParameters.Add("@Postcode" ,
Me.PostCodeTextBox.Text)

I have checked parameter names etc put it wont put the values to the
parameters. Obviously the .Add is wrong, what should be using?

Add is correct, but it doesn't want to tell you it needs to know the data
type (e.g. SqlDbType.NVarChar) and, for strings, the length.

Andrew

[cbrown]

Depending on what your insert command is called, if you used a
SqlCommand object then it will go like this...

SqlCommand1.Parameters("@CompanyName").Value = CompanyNameTextBox.Text

[MC]

I must be doing something totally wrong. ADO is confussing me but I am
getting there.

OK and using V2 .net. I could have used a formview but choose not to cause
it does some validation on fields as I go and it was to confusing using the
formview.

So I made my own form and I get the validations working fine.

I hit insert button i created and then with SqlDataSource1 I dragged on to
the webpage (with Insert Command in toe) i created the following procedure.

Protected Sub InsertTextBox_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles InsertTextBox.Click

Me.SqlDataSource1.InsertParameters.Add("@CompanyID ", Me.CompIDTextBox.Text)

Me.SqlDataSource1.InsertParameters.Add("@ContactNa me",
Me.ContactNameTextBox.Text)

Me.SqlDataSource1.InsertParameters.Add("@CompanyNa me",
Me.CompanyNameTextBox.Text)

Me.SqlDataSource1.InsertParameters.Add("@Postcode" , Me.PostCodeTextBox.Text)

Try

Me.SqlDataSource1.Insert()

Catch ex As Exception

Response.Write(ex.Message)

End Try

End Sub
Which as you know I listed earlier. The Insert Command is as shown below:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:connEducare %>"

DeleteCommand="DELETE FROM [Companies] WHERE [ID] = @original_ID"

InsertCommand="INSERT INTO [Companies] ([CompanyID], [ContactName],
[TelNumber], [FaxNumber], [email], [CompanyName], [Address], [Postcode],
[Username], [Password]) VALUES (@CompanyID, @ContactName, @TelNumber,
@FaxNumber, @Email, @CompanyName, @Address, @Postcode, @Username,
@Password)"

SelectCommand="SELECT * FROM [Companies]" UpdateCommand="UPDATE [Companies]
SET [CompanyID] = @CompanyID, [ContactName] = @ContactName, [TelNumber] =
@TelNumber, [FaxNumber] = @FaxNumber, [email] = @Email, [CompanyName] =
@CompanyName, [Address] = @Address, [Postcode] = @Postcode, [Username] =
@Username, [Password] = @Password WHERE [ID] = @original_ID">

<DeleteParameters>

<asp:Parameter Name="original_ID" Type="Int32" />

</DeleteParameters>

<UpdateParameters>

<asp:Parameter Name="CompanyID" Type="Int32" />

<asp:Parameter Name="ContactName" Type="String" />

<asp:Parameter Name="TelNumber" Type="String" />

<asp:Parameter Name="FaxNumber" Type="String" />

<asp:Parameter Name="Email" Type="String" />

<asp:Parameter Name="CompanyName" Type="String" />

<asp:Parameter Name="Address" Type="String" />

<asp:Parameter Name="Postcode" Type="String" />

<asp:Parameter Name="Username" Type="String" />

<asp:Parameter Name="Password" Type="String" />

<asp:Parameter Name="original_ID" Type="Int32" />

</UpdateParameters>

<InsertParameters>

<asp:Parameter Name="CompanyID" Type="Int32" />

<asp:Parameter Name="ContactName" Type="String" />

<asp:Parameter Name="TelNumber" Type="String" />

<asp:Parameter Name="FaxNumber" Type="String" />

<asp:Parameter Name="Email" Type="String" />

<asp:Parameter Name="CompanyName" Type="String" />

<asp:Parameter Name="Address" Type="String" />

<asp:Parameter Name="Postcode" Type="String" />

<asp:Parameter Name="Username" Type="String" />

<asp:Parameter Name="Password" Type="String" />

</InsertParameters>

</asp:SqlDataSource>

And then I get the following error:

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

So confused

[cbrown]

First try changing this line to...

Me.SqlDataSource1.InsertParameters.Add("@CompanyID ", 32)

Or some other Integer that doesnt violate the database constraints.

[Andrew Morton]

MC wrote:

Thanks Andrew,

So whats the syntax?

I do it the long way round, e.g:-

Dim sqlCmd As New SqlCommand("AddBasket", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

Dim sqlParam As New SqlParameter("@UserID", SqlDbType.Char, 36)
sqlParam.Value = myUserID
sqlCmd.Parameters.Add(sqlParam)

sqlParam = New SqlParameter("@BasketName", SqlDbType.NVarChar, 24)
sqlParam.Value = basketName
sqlCmd.Parameters.Add(sqlParam)

' etc.

Andrew

[cbrown]

Great example Andrew,
I also use the same method you do. IMHO, I think it is a little
cleaner than the way VS adds things.

[Cor Ligthert [MVP]]

MC,

It is not so important what you use, if you use the parameters only once,
than you can use your method.

If you will use them more by instance as you do, than at the second button
click you are in trouble, because it is a collection, so you have than 8
etc. parameters while 4 are expected.

Therefore the methode from Andrew has a little bit to be changed..

Form Load event.
Dim sqlCmd As New SqlCommand("AddBasket", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure
Dim sqlParam As New SqlParameter("@UserID", SqlDbType.Char, 36)
sqlCmd.Parameters.Add(sqlParam)
sqlParam = New SqlParameter("@BasketName", SqlDbType.NVarChar, 24)
sqlCmd.Parameters.Add(sqlParam)

Button Event
sqlCmd.Parameters("@UserID").Value = myUserID
sqlCmd.Parameters("@BasketName").Value = basketName

I hope this gives an idea.

Cor
<cb****@duclaw.com> schreef in bericht
news:11**********************@g43g2000cwa.googlegr oups.com...

Great example Andrew,
I also use the same method you do. IMHO, I think it is a little
cleaner than the way VS adds things.