I want to insert a record with fields let say"ProductCode, Name" to a table with records like "ProductCode, Name, NumberofItems". I get mismatching errors "Conversion from string "INSERT INTO dbo.t_Shopping(Productcod" to type 'Double' is not valid.". Note the missing last letter. ProductCode and Name are both varChars, NumberofItems is int in table and double in my code.
This is my insertion script ".CommandType = CommandType.Text = "INSERT INTO dbo.t_Shopping(ProductCode, Name, NumberofItems)SELECT ProductCode, Name, @.NumberofItems FROM dbo.t_Product WHERE ProductCode=@.ProductCode"
I have asked this question previously in an other mail, but probably because that thread was marked solved, no one answered.
The whole code is like this: I translated some names. I hope its still valid.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim NumberofItems As Double = 1
Dim conn As New SqlConnection("Data Source=DV2\SQLEXPRESS;Initial Catalog=testaus;Integrated Security=True")
Dim row As DetailsViewRow = DetailsView1.Rows(0)
Dim ProductCode As String = row.Cells(1).Text
Dim cmd As New Data.SqlClient.SqlCommand
Dim param As New SqlParameter()
TextBox1.Text = ProductCode
With cmd
.Connection = conn
.CommandType = CommandType.Text = "INSERT INTO dbo.t_Shopping(ProductCode, Name, NumberofItems)SELECT ProductCode, Name, @.NumberofItems_
FROM dbo.t_Product WHERE ProductCode=@.ProductCode"
.Parameters.AddWithValue("@.ProductCode", ProductCode)
.Parameters.AddWithValue("@.NumberofItems", NumberofItems) '
End With
Try
conn.Open()
cmd.ExecuteNonQuery()
Catch ex As Data.SqlClient.SqlException
Throw New ApplicationException("An error occurred while trying to insert the record")
Finally
conn.Dispose()
End Try
Regards
Leif
My Sql command code had problems, probably. I tried this (see below), but got still more unclearer error. "Incorrect syntax near ','.".I dont know if the problem is in sql query string or in code using 2 parameters,or in wrong variable types.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim NumberofItems As Integer = 1
Dim luku As Integer
Dim conn As New SqlConnection("Data Source=DV2\SQLEXPRESS;Initial Catalog=testaus;Integrated Security=True")
Dim row As DetailsViewRow = DetailsView1.Rows(0)
Dim ProductCode As String = row.Cells(1).Text
TextBox1.Text = ProductCode
Dim cmd As New SqlCommand("insert dbo.t_Shopping (ProductCode, Name, NumberofItems) select (ProductCode, Name,@.NumberofItems) FROM dbo.t_Product_
WHERE ProductCode=@.ProductCode", conn)
Dim partk, paramaar As New SqlParameter()
partk.Value = ProductCode
partk.SqlDbType = SqlDbType.VarChar
partk.Direction = ParameterDirection.Input
partk.ParameterName = "@.ProductCode"
cmd.Parameters.Add(partk)
paramaar.Value = NumberofItems
paramaar.SqlDbType = SqlDbType.Int
paramaar.Direction = ParameterDirection.Input
paramaar.ParameterName = "@.NumberofItems"
cmd.Parameters.Add(paramaar)
cmd.Connection.Open()
luku = cmd.ExecuteNonQuery() 'This gives errors
cmd.Connection.Close()
End Sub
Leif
|||
LISM:
Dim cmd As New SqlCommand("insert dbo.t_Shopping (ProductCode, Name, NumberofItems) select (ProductCode, Name,@.NumberofItems) FROM dbo.t_Product_
WHERE ProductCode=@.ProductCode", conn)
First, remove the () from the select query.
Second, make sure all the parameters are actually having some values.
Hope this will help.
|||
Thanks
Is there difference between NumberofItems and @.NumberofItems and so on. Variables without @. have a value, but with them don't. Besides, I now tried to @.NumberofItems =NumberofItems and it didn't help (got errors).
Whats next?
Leif
|||
It depends on what you want to use. @.NumberofItems seems to be what user typed in and NumberofItems seems to be coming from the table.. so its your call on which should go into the table.
|||
Thank you for an answer
It looks like I have misunderstood this.
NumberOfItem comes now from code. Its value is 1 now. And yes, someday it may come from the user. Then I try to put it in the Shopping table. I try to do it this way because in Shopping table there is an extra field called NumberofItems. Shopping is a shopping basket and it should contain amount field. The Product table is really a list of names and codes.
Query should work so that it looks for an iten whose code is in ProductCode or @.ProductCode. And then copies that some fields of that record plus NumberOfItems field to shopping basket.
I don't really know where the problem is, in code or in query.
Regards
Leif
|||
Just change your query to
INSERTdbo.t_Shopping (ProductCode, Name, NumberofItems)
SELECT ProductCode, Name,@.NumberofItems
FROMdbo.t_Product
WHERE ProductCode=@.ProductCode
as dhimant suggested and it would work.
|||
Sorry about the mess. But it works!
There was still some problem with code or query, and I wanted to ask more help. Anyway I tried the query in this code, and at first I got errors:"Incorrect syntax near ')'. This is my code without translation. I left out name field to get shorter lines. Tuotekoodi=Productcode, Osto~Shopping, Maara=numberOfItems and so on.
Dim cmd As New SqlCommand("INSERT dbo.t_Osto (Tuotekoodi, Maara) SELECT Tuotekoodi,@.Maara FROM dbo.t_Tuote WHERE Tuotekoodi=@.Tuotekoodi", conn)
cmd.Parameters.Add("@.Maara", SqlDbType.Int)
cmd.Parameters.Add("@.Tuotekoodi", SqlDbType.VarChar)
conn.Open()
cmd.Parameters("@.Tuotekoodi").Value = Tuotekoodi
cmd.Parameters("@.Maara").Value = Maara
luku = cmd.ExecuteNonQuery()
conn.Close()
The most difficult and interesting part was the fact that the IDE inserted some extra brackets () . I copy/pasted your code to an empty place in my code, and got many errors, thats ok. But at the same time IDE inserted couple of pairs of brackets there. And then I pasted the code with extra brackets to my code. That was quite difficult to see. I tend to trust Copy/paste. A nasty feature?
Probably that spoiled my earlier attempts, also.
And lets not forget, it works, thanks.
Leif
. You need to search for the right formatting of your input data and you'll find the way.