Inserting into database an sql statement

Question

I'm trying to insert into the database an sql statement as in one of the variables hold an sql statement. This statement however has parameters and it shows up in the database as @parm instead of the actual values. Any ideas?

This is the statement:

    Dim cmd As New SqlCommand("insert into MyTableOne (ID, QueryText) values         (3,'insert into MyTableTwo (Name,Image,ClientId,Taskcode,TaskYear,Notes,ImgDT,Flag) values   (@name,@img,@clientid,@taskcode,@taskyear,@notes,@imgdt,@flag)')", con)

this is how im filling in the values cmd.Parameters.AddWithValue("@name",Name) and Name is a value given as a parm in the method (this is all in a web service method then called from an app that provides the values)

New:

   Dim cmd As New SqlCommand("insert into FTWebUploadQueue2_GVT (ID, QueryText) values (4,'insert into IMAGES_GVT (Name,Image,ClientId,Taskcode,TaskYear,Notes,ImgDT,Flag) values ('+ @name + ',' + @img + ',' + @clientid + ',' + @taskcode + ',' + @taskyear + ',' + @notes + ',' + @imgdt + ',' + @flag +')')", con)

    <WebMethod()> _
        Public Function SaveImageInBackoffice(ByVal imageData As Byte(), ByVal Name As String, ByVal ClientId As String, ByVal Taskcode As Integer, ByVal TaskYear As Integer, ByVal Notes As String, ByVal ImgDT As DateTime, ByVal Flag As Integer) As Boolean

    Dim con As New SqlConnection("Server=****;uid=**;pwd=****;database=ImagesTrial")
    If con.State = ConnectionState.Closed Then con.Open()

    Dim cmd As New SqlCommand("insert into FTWebUploadQueue3_GVT (ID, QueryText) values (4,'insert into IMAGES_GVT (Name,Image,ClientId,Taskcode,TaskYear,Notes,ImgDT,Flag) values ('+ CAST(@name AS VARCHAR) + ',' + CAST(@img AS VARCHAR) + ',' + CAST(@clientid AS VARCHAR) + ',' + CAST(@taskcode AS VARCHAR) + ',' + CAST(@taskyear AS VARCHAR) + ',' + CAST(@notes AS VARCHAR) + ',' + CAST(@imgdt AS VARCHAR) + ',' + CAST(@flag AS VARCHAR) +')')", con)

    cmd.Parameters.AddWithValue("@img", imageData)
    cmd.Parameters.AddWithValue("@name", Name)
    cmd.Parameters.AddWithValue("@clientid", ClientId)
    cmd.Parameters.AddWithValue("@taskcode", Taskcode)
    cmd.Parameters.AddWithValue("@taskyear", TaskYear)
    cmd.Parameters.AddWithValue("@notes", Notes)
    cmd.Parameters.AddWithValue("@imgdt", ImgDT)
    cmd.Parameters.AddWithValue("@flag", Flag)
    cmd.ExecuteNonQuery()
    con.Close()
    Return 0
End Function

All of the @___ parameters are within single quotes, so it is a string. — , Dec 26 '13 at 07:14
I tried it with single quotes around all @parms, I get that the server was unable to process the request. I actually dont quite understand what you meant — Farah, Dec 26 '13 at 07:19
You are providing it a literal string: '...@name,@img...' are not parameters they are text. — , Dec 26 '13 at 07:28
Yes, you have to make sure the parameters are outside of the single quotes - this means you will likely have to concatenate the statement. One way: `'... values (' + @name + ',' + @img + [and so on...]'` — , Dec 26 '13 at 07:35
Thank you so much for the suggestion, I edit the post to my new query however it gives an error says that some of the data types are incompatible with the concatenate expression, "The data types nvarchar and varbinary are incompatible in the add operator" — Farah, Dec 26 '13 at 07:44
That's because they are probably not all some type of text. You need to cast them as text (varchar, nvarchar, etc.) Additionally, you need to be concerned with commas or single quotes within the values as they could cause your query to error out. This is not ideal - maybe you could elaborate more on what your goal of this is? — , Dec 26 '13 at 07:46
Thank you again for your help! Some values are actually integers and another is a byte array referring to an image, so how could i go around that? The purpose of this is that i was asked to insert the query into the database as part of moving an image from a smart device application to the back end office. — Farah, Dec 26 '13 at 07:53
You need to individually convert them to text. Example: `CAST(@TaskYear AS VARCHAR)` It's hard to offer a better solution without seeing more of what you are doing. — , Dec 26 '13 at 07:56
If I cast I get incorrect results actually the query doesnt go beyond the name parm "insert into IMAGES_GVT (Name,Image,ClientId,Taskcode,TaskYear,Notes,ImgDT,Flag) values (farah1,ÿØÿà" — Farah, Dec 26 '13 at 08:04
Again - maybe you could elaborate on what your goal is here. There is probably a much better way to approach this. I'd need more information to advise any further. — , Dec 26 '13 at 08:05
Thank you for trying to help again! So i edited my question with my whole method. The point of this method is to insert the statement into the database as an sql query. I want to later retrieve this statement and execute it. What the statement does is that it saves some data i got from the database on my pda. Im calling this web service from an application, this application retrieves the data including the image i want and does some processing to insure the image is saved in a correct manner on my back end database. — Farah, Dec 26 '13 at 08:12
Check out the answer I provided and let me know if that helps. Your additional code was helpful. — , Dec 26 '13 at 08:40

score 0 · Answer 1 · 2013-12-26T08:28:19.847

0

    <WebMethod()> _
        Public Function SaveImageInBackoffice(ByVal imageData As Byte(), ByVal Name As String, ByVal ClientId As String, ByVal Taskcode As Integer, ByVal TaskYear As Integer, ByVal Notes As String, ByVal ImgDT As DateTime, ByVal Flag As Integer) As Boolean

    Dim con As New SqlConnection("Server=****;uid=**;pwd=****;database=ImagesTrial")
    If con.State = ConnectionState.Closed Then con.Open()

    Dim qry As New SqlCommand("insert into IMAGES_GVT (Name,Image,ClientId,Taskcode,TaskYear,Notes,ImgDT,Flag) values (@name,@img,@clientid,@taskcode,@taskyear,@notes,@imgdt,@flag", con)

    qry.Parameters.AddWithValue("@img", imageData)
    qry.Parameters.AddWithValue("@name", Name)
    qry.Parameters.AddWithValue("@clientid", ClientId)
    qry.Parameters.AddWithValue("@taskcode", Taskcode)
    qry.Parameters.AddWithValue("@taskyear", TaskYear)
    qry.Parameters.AddWithValue("@notes", Notes)
    qry.Parameters.AddWithValue("@imgdt", ImgDT)
    qry.Parameters.AddWithValue("@flag", Flag)
    Dim query as String = qry.CommandText

    Dim cmd As New SqlCommand("insert into FTWebUploadQueue3_GVT (ID, QueryText) values (4,@query", con)
    cmd.Parameters.AddWithValue("@query", query)
    cmd.ExecuteNonQuery()
    con.Close()
    Return 0
End Function

This lets the SqlClient library do it's job with parameters. You can get the prepared statement/command text that would be executed and, in turn, pass that as a parameter value in the actual command you are to execute.

edited Dec 26 '13 at 08:28

answered Dec 26 '13 at 08:21

Thank you again.. The aoutput of this is the query with the @parms in the query string actually. To get the query text though i thought of using this method: query = cmd.CommandText Dim p As SqlParameter For Each p In cmd.Parameters query = query.Replace(p.ParameterName, p.Value) Next – Farah Dec 26 '13 at 08:43
this gives all values correct however the byte array and date are of wrong value. Any ideas? – Farah Dec 26 '13 at 08:44
What are their values before and after? – Dec 26 '13 at 08:45
this is what i get in the database: insert into IMAGES_GVT (Name,Image,ClientId,Taskcode,TaskYear,Notes,ImgDT,Flag) values (farah1,System.Byte[],1,1,2013,hi,System.Byte[]dt,0) – Farah Dec 26 '13 at 08:48
My suspicion is that the query would actually work, but obviously not the text representation. You may indeed need to convert those two values before passing them in. For example: `qry.Parameters.AddWithValue("@imgdt", ImgDT.ToString()) 'Assuming ImgDT is of a datetime type` – Dec 26 '13 at 09:09
I actually need to deal with the image only. the image i converted into a binary array however when i convert that to a string im getting wrong data – Farah Dec 26 '13 at 09:46
You have to make sure you are converting it correctly (.ToString() wont be correct): http://msdn.microsoft.com/en-us/library/ms172827.aspx – Dec 26 '13 at 09:49
no im using this method: cmd.Parameters.AddWithValue("@img", System.Text.Encoding.UTF8.GetString(imageData)) – Farah Dec 26 '13 at 10:04
if it helps this is how i converted it to a byte array: Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte()) – Farah Dec 26 '13 at 10:05
See if this helps you: http://stackoverflow.com/questions/311165/how-do-you-convert-byte-array-to-hexadecimal-string-and-vice-versa – Dec 26 '13 at 10:15

Inserting into database an sql statement

1 Answers1