Converting data to be inserted into a database

Hi,

I am using web matrix, and I am trying to insert a data into a MSDE database. I have used webmatrix to generate the update code, and it is executed when a button is pressed on the web page. but when the code is executed I get the error:

Syntax error converting the varchar value 'txtAmountSold.text' to a column of data type int.

So I added the following code to try to convert the data, but i am still getting the same error, with txtAmountSold.text replaced with "test"

dim test as integer
test = Convert.ToInt32(txtAmountSold.text)

Here is the whole of the function I am using:

Function AddItemToStock() As Integer

dim test as integer
test = Convert.ToInt32(txtAmountSold.text)

Dim connectionString As String = "server='(local)\Matrix'; trusted_connection=true; database='HawkinsComputers'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _
"], [Description], [image], [OnOffer], [OfferPr"& _
"ice], [OfferDescription], [AmountInStock], [AmountOnOrder], [AmountSold]) VALUES ('CatList.SelectedItem.text', 'txtType.text', 'txtname.text', 'txtmanufacturer.text'"& _
", convert(money,'txtPrice.text'), 'txtWeight.text', 'txtDescription.text', 'txtimage.text', 'txtOnOffer"& _
".text', convert(money,'txtOfferPrice.text'), 'txtOfferDescrip"& _
"tion.text', 'txtAmountInStock.text', 'txtAmountOnOrder.text', 'test')"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Return rowsAffected
End Function

Any help in solving this problem would be greatly appreciated, as I am really stuck for where to go next.The probably is that you are trying to send the literal value 'test' as a parameter value.

Please use this approach instead:
Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _
"], [Description], [image], [OnOffer], [OfferPr"& _
"ice], [OfferDescription], [AmountInStock], [AmountOnOrder],[AmountSold]) VALUES (@.CatList, @.Type,@.name, @.manufacturer, @.Price, @.Weight, @.Description, @.image, @.OnOffer, @.OfferPrice, @.OfferDescription, @.AmountInStock, @.AmountOnOrder, @.test)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.Parameters.Add("@.CatList,SqlDbType.VarChar,99).Value = CatList.SelectedItem.text
dbCommand.Parameters.Add("@.Type,SqlDbType.VarChar,50).Value =txtType.text
dbCommand.Parameters.Add("@.Name,SqlDbType.VarChar,30).Value =txtmanufacturer.text
' add all parameters in this manner
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Note that for each parameter you will need to use the appropriate SqlDbType. And if it's a character data type you will also need to specify the length.

Here are some links which should help you with using parameters:
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net
How To: Protect From SQL Injection in ASP.NET|||Thanks alot for the reply, Those links will help alot aswell.

Convert row with text field to column

Hi experts;

How can I generate the result using typical SQL statement based on the following tables?

Table a - Salesman (salesId, Name) pk : salesId

Table b - Invoice(InvoiceNo, salesId, InvoiceAmt) pk : invoiceNo fk : salesId -> Table a

The result set :

salesId, Name, sum(InvoiceAmt), InvoiceNos with comma separator

For example:

Table a

SalesId Name

S001 Peter

S002 Alice

Table b

InvoiceNo SalesId InvoiceAmt

INV001 S001 $100

INV002 S001 $100

INV003 S001 $400

INV004 S002 $200

Result set

SalesId Name Sum(InvoiceAmt) InvoiceNos

S001 Peter $600 INV001, INV002, INV003

S002 Alice $200 INV004

Thanks

Hi,

You can use the COALESCE function in order to get columns of more than one row as a string expression.

You can find samples at below links.

http://www.kodyaz.com/articles/article.aspx?articleid=29
http://www.kodyaz.com/forums/thread/76.aspx

It is better to create a user defined function which uses the Coalesce and then call this function in your select queries to get related list of invoice no's as one string seperated with commas.

A sample function may be like the below one.

CREATE FUNCTION udf_GetFileList

(

@.RelatedPKID int

)

RETURNS nvarchar(4000)

AS

BEGIN

Declare @.Files nvarchar(4000)

SELECT

@.Files = COALESCE(@.Files + ' ' ,'') + SystemFileName + ','

FROM

AttachedFiles

WHERE

RelatedPKID = @.RelatedPKID

RETURN @.Files

END

GO

I hope this helps.

Eralper

http://www.kodyaz.com

|||

Check out the techniques in this article:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

The "select @.variable = @.variable + columnValue" approach has its dangerous limitations, and should be avoided if possible (It is a neat trick that I have used too, so I am not trying to moralize, just note the issues :)

In 2005, however, there is a much better way of doing things using the XML, functionality, which is outlined in that article.

|||

Hi Eralper;

Thank you. It worked.

You see my SQL as follows:

create function get_invlist
( @.SalesID NvarChar(10))
Returns nvarchar(4000)
as begin

declare @.inv NvarChar(4000);
select @.inv=COALESCE(@.inv + '','')+InvoiceId +',' from sales, invoice
where
sales.salesid = invoice.salesid
and sales.salesid = @.SalesID
Return @.inv
end
go

end function

select sales.salesid, salesName, sum(invamt), test.dbo.get_invlist(sales.salesid)
from sales, invoice
where
sales.salesid = invoice.salesid
group by sales.salesid, salesname;

Result

S0001 Peter 600.00 INV001 ,INV002 ,INV003 ,
S0002 Alice 500.00 INV004 ,

Regards.

|||

Hi Louis;

I agreed you that using @.variable will have limitations. I will test your suggestion later.

Could I use classical SQL to implement this kind of requirement? (i.e. not use @.variable or XML or new function)

Thanks

|||

Hi Louis;

I tried your method. It is better than before.

Please see my SQL.

select sales.salesid, salesName,
Replace((select rtrim(InvoiceId)
as "data()"
from invoice
where
sales.salesid = invoice.salesid
for XML PATH ('')), ' ', ',')
as "@.InvID"
from sales
group by sales.salesid, salesname;

It is so dynamic and easy to understand.

Thanks.

|||I wish it was my method :) It was Aaron Bertrand's. Glad it helped.