creating sql parameters even the sql command text has no parameter added

systan
systan used Ask the Experts™
on
creating sql parameters even the sql command text has no parameter added
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul JacksonSoftware Engineer
Top Expert 2011
Commented:
PLease post some code so we can understand what it is your trying to do
Most Valuable Expert 2012
Top Expert 2014

Commented:
Without looking at the code, I would say no.

If creating parameter is all that you want then just instantiate the SQLParameter class

Author

Commented:
Ok, I think the question or codes are not clear,
Let me try to explain,
I'm saving or updating database files to local computer(server) and to network computers.

With this script, I can save to any computers, on local and network(TCPclient)
    
Dim Script As String = "insert into expences(dtime,expencefor,cash,byemp) values (" & "'" & DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") & "'" & "," & "'" & txtexp & "'" & "," & txtcash & "," & "'" & Txtexpences.Tag.ToString & "'" & ")"
Private Sub SaveSendReceivedExpences(ByVal script As String)
        Try
            Dim sc As New SQLiteCommand(script, mycon)
            sc.ExecuteNonQuery()
            sc.Parameters.Clear()
            sc.Dispose()
        Catch ex As Exception
        End Try
    End Sub

Open in new window


BUT, I'm tired using/typing single quatations like the script above.

So, I decided to split the Script.
Private Sub SaveSendReceivedExpences(ByVal script As String, ByVal culture As String)
        Dim sc As New SQLiteCommand(script, mycon)
''//THIS SAVED IN LOCAL SERVER DATABASE FILE
        If culture.Equals("local") Then
            Dim dtnow As DateTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt")
            sc.Parameters.Add("$dt", DbType.DateTime).Value = dtnow
            sc.Parameters.Add("$exp4", DbType.String).Value = Txtexpences.Text
            sc.Parameters.Add("$cas", DbType.Decimal).Value = txtcashexpences.Text
            sc.Parameters.Add("$byempl", DbType.String).Value = Txtexpences.Tag.ToString
       
        MessageBox.Show("SAVING TO LOCAL SERVER")
        Else
           
            'Dim i As Integer = script.IndexOf("values")
            'Dim items As String = script.Substring(i + 6, Len(script) - (i + 6)).TrimStart(" ")
            'items = items.TrimStart("(")
            'items = items.TrimEnd(")")
            'Dim value(items.Split(",").Length) As String
            'Dim x As Integer = 0
            'For Each item As String In items.Split(",")
            '    value(x) = item
            '    i += 1
            'Next

''//DISABLED BECAUSE IT DOESN'T SAVED ANYTHING
            'sc.Parameters.Add("$value(0)", DbType.DateTime).Value = value(0)
            'sc.Parameters.Add("$value(1)", DbType.String).Value = value(1)
            'sc.Parameters.Add("$value(2)", DbType.Decimal).Value = Convert.ToDecimal(value(2))
            'sc.Parameters.Add("value(3)", DbType.String).Value = value(3)
            
        MessageBox.Show("SAVING FROM NETWORK, BUT NOT SAVING")
        End If
        sc.ExecuteNonQuery()
        sc.Parameters.Clear()
        sc.Dispose()
    End Sub

Open in new window


The script when I saved to local: SaveSendReceivedExpences(Script, "local")
Dim Script As String = "insert into expences(dtime,expencefor,cash,byemp) values ($dt, $exp4, $cas, $byempl)"

Open in new window


The script when I save to Networks: SaveSendReceivedExpences(Script, "network")
Dim Script As String = "insert into expences(dtime,expencefor,cash,byemp) values (" & "'" & DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") & "'" & "," & "'" & txtexp & "'" & "," & txtcash & "," & "'" & Txtexpences.Tag.ToString & "'" & ")"

Open in new window


BUT again I'm tired of using the script for network with many quotations. "'"

On TCPclient, when received is like this:
if msg.StartsWith("insert in expences") then
SaveSendReceivedExpences(msg, "Network")
endif
The message received is the script with many quotations.

I can't use $ or parameter if I send it to network.

So, I decided to ask if is possible and I hope there is a solution.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Most Valuable Expert 2012
Top Expert 2014

Commented:
>Ok, I think the question or codes are not clear,

There was no code and the question was as brief as it could get.

>So, I decided to ask if is possible and I hope there is a solution.

Still not sure what you are trying to achieve. You know the concepts of parameters. These are place holders in sql statement for which you supply values separately. If there are no parameters in sql statement, how can you set the values?

Is it not your script? Can you not change it to same as "local" script?

Author

Commented:
>Still not sure what you are trying to achieve. You know the concepts of parameters. These are place holders in sql statement for which you supply values separately. If there are no parameters in sql statement, how can you set the values?
I can't, we can't?
>Is it not your script? Can you not change it to same as "local" script?
Yes, I can, but I have to write it with lots of quotation mark

Ok,
let me try to explain it again with codes, if possible to have a solution.
private Sub button_save
...
...
Dim Script1 As String = "insert into expences(dtime,expencefor,cash,byemp) values ($dt, $exp4, $cas, $byempl)"
SaveSendReceivedExpences(script1, "local")
...
Dim Scrip2t As String = "insert into expences(dtime,expencefor,cash,byemp) values (" & "'" & DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") & "'" & "," & "'" & txtexp & "'" & "," & txtcash & "," & "'" & Txtexpences.Tag.ToString & "'" & ")"
SenderThread.BeginInvoke(Script2, NOTHING, NOTHING)
End Sub

Open in new window


''//Message Received from Network by <SenderThread>
private Sub Thread_Receiver_Msg_From_Network
...
...
if MSG.StartsWith("insert into expences") then
SaveSendReceivedExpences(MSG, "network")
Endif
...
...
End Sub

Open in new window



As you can see in the codes, I have 2 different script or command Text
If I want to have only 1 script then I have to go through Script2
But script2 has many commas and quotation marks
This will be the Method to save in both local and network,
As you ask if I can change it to same as "local" script?
private Sub button_save   
...
...
Dim 1Script_for_Both As String = "insert into expences(dtime,expencefor,cash,byemp) values (" & "'" & DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") & "'" & "," & "'" & txtexp & "'" & "," & txtcash & "," & "'" & Txtexpences.Tag.ToString & "'" & ")"
SaveSendReceivedExpences(1Script_for_Both)
...
SenderThread.BeginInvoke(1Script_for_Both, NOTHING, NOTHING)
End Sub

Private Sub SaveSendReceivedExpences(ByVal script As String)
        Try
            Dim sc As New SQLiteCommand(script, mycon)
            sc.ExecuteNonQuery()
            sc.Parameters.Clear()
            sc.Dispose()
        Catch ex As Exception
        End Try
    End Sub

Open in new window


The problem for that, has to many commas and marks,
I want to avoid using it, so I decided to use parameters if possible with network sending.
Since I could not use parameters in 1script_for_both, because when MSG received it can't understand $ or parameter value, it has to be a value assigned.

I'm asking if there is a possible solution or other ideas that I can use parameter to avoid typing commas and quotation marks.
Most Valuable Expert 2012
Top Expert 2014
Commented:
I am still lost. May be someone else can understand.

May be you want to change

Dim Scrip2t As String = "insert into expences(dtime,expencefor,cash,byemp) values (" & "'" & DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") & "'" & "," & "'" & txtexp & "'" & "," & txtcash & "," & "'" & Txtexpences.Tag.ToString & "'" & ")"


to


Dim Scrip2t As String = "insert into expences(dtime,expencefor,cash,byemp) values (@date,@txtexp,@txtcash,@Txtexpences)"

And then just set the parameter values.

Author

Commented:
I think its going complicated to understand.

Let me try other words or other question to closed this thread any time.

Do you have any  functions that will provide this kind of function ways:
sample:
autoinsertany("expencestable", "field1name", "field2name", "field3name", "field4name","field1value","field2value","field3value", "field4value")

autoinsertany("attendancetable", "field1name","field2name","field1value","field2value")

autoinsertany("reporttable", "field1name", "field2name", "field3name", "field1value","field2value","field3value")

or
 autoinsertany("expences", "dtime,expencefor,cash,byemp", TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text)


So, that I would not write a command Text with single quotation marks, because my application can't use parameter.


Here's my code sample, please someone could finish it, I'm lost.
Private Sub autoinsertany(ByVal xtable As String, ByVal xfields As String, ByVal ParamArray p() As Object)
        Dim insert As String = "insert into " & xtable
        Dim closeopen As String = "(" & xfields & ")"
        Dim val As String = "values"

        For Each pp As Object In p
            If TypeOf pp Is String Then
                ''//dont know, can't complete
            ElseIf TypeOf pp Is Decimal Then
                ''//dont know, can't complete
            ElseIf TypeOf pp Is Boolean Then
                ''//dont know, can't complete
            End If
        Next
        Dim Complete_P_with_Quotation_and_Commas as STRING = "" 'no idea here
        Dim vcloseopen As String = "(" & Complete_P_with_Quotation_and_Commas & ")"

        ''//HERES the main point, to automate inserting without using quotation mark always
        Dim sqltext As String = insert & closeopen & val & vcloseopen
       
        Dim sqlCommand as SqlCommand(sqltext, theconnection)
        sqlCommand.ExecuteNonQuery()
        sqlCommand.Dispose()

    End Sub

Open in new window

thanks
Didn't look through all the correspondings but are you trying to achieve passing variables for a sql statement to get the correct data at the same time the "Sql statement" cannot have quotes?

If that's the case, create Stored Procedure with parameters, when you pass the script, it's just the name of the stored procedure, then no issue...

Is that what you are trying to achieve?

I don't see what is the differences when you wish to save local or network to do with the sql statement. You just need different connection strings

Author

Commented:
I'm using sqlite database file, that's why I could not use any other database server, I just need this function to finished.
Private Sub SaveSendReceivedExpences(ByVal xtable As String, ByVal xfields As String, ByVal ParamArray p() As Object)
        Dim insert As String = "insert into " & xtable
        Dim closeopen As String = "(" & xfields & ")"
        Dim val As String = "values"

        For Each pp As Object In p
            If TypeOf pp Is String Then
                ''//dont know, can't complete
            ElseIf TypeOf pp Is Decimal Then
                ''//dont know, can't complete
            ElseIf TypeOf pp Is Boolean Then
                ''//dont know, can't complete
            End If
        Next
        Dim Complete_P_with_Quotation_and_Commas as STRING = "" 'no idea here
        Dim vcloseopen As String = "(" & Complete_P_with_Quotation_and_Commas & ")"

        ''//HERES the main point, to automate inserting without using quotation mark always
        Dim sqltext As String = insert & closeopen & val & vcloseopen
       
        Dim sqlCommand as SqlCommand(sqltext, theconnection)
        sqlCommand.ExecuteNonQuery()
        sqlCommand.Dispose()

    End Sub
                                           

So, it will look like this:

''//Saving records both to local and network
private Sub Button_save
...
...
Dim Script As String = "expences", "dtime,expencefor,cash,byemp", TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text"
...
SaveSendReceivedExpences(script)
...
''//SENDS Message to ALL NETWORKS
SenderThread.BeginInvoke(Script, NOTHING, NOTHING)
End Sub
...
 ''//Received Message from Network                                          
private Sub Thread_Receiver_Msg_From_Network
...
...
if MSG.StartsWith("insert into expences") then
SaveSendReceivedExpences(MSG)
Endif
if MSG.StartsWith("insert into attendance") then
SaveSendReceivedExpences(MSG)
Endif
...
...
End Sub
Most Valuable Expert 2012
Top Expert 2014

Commented:
How many SQL statements are you going to produce? Is it a lot?
Will this works for you? I have these function where so long you pass Tablenames, fieldnames and parameters, it would work regardless of datatypes. You
just conver to CE style.

   Public Overloads Sub psUpdateRecord(ByVal strTable As String, ByVal alFields As ArrayList, ByVal alParameters As ArrayList)

            Dim strSQL As String
            Dim iStrCount As Integer

            Dim strField As String
            strField = "Para"

            strSQL = "UPDATE " & strTable & " SET "

            For iStrCount = 0 To alFields.Count - 1
                strSQL = strSQL & alFields.Item(iStrCount) & "=@" & strField & iStrCount
                If iStrCount <> alFields.Count - 1 Then
                    strSQL = strSQL & ","
                End If
            Next

            Dim cmdUpdateMenu As New SqlClient.SqlCommand(strSQL, Conn)
            Try
                For iStrCount = 0 To alFields.Count - 1
                    cmdUpdateMenu.Parameters.Add(New SqlClient.SqlParameter("@" & strField & iStrCount, alParameters.Item(iStrCount)))
                Next

                cmdUpdateMenu.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox("Error Number: " & Err.Number & vbCrLf & "Description: " & Err.Description)
            Finally
                cmdUpdateMenu.Dispose()
            End Try

        End Sub
Commented:
I have used a wrong method, it should be BinaryFormatter.
http://social.msdn.microsoft.com/Forums/en/netfxremoting/thread/23ff50f3-63c1-4e6c-a17b-801ad0979ce8
thanks to all

Author

Commented:
I have exactly what I wanted

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial