Avatar of systan
systan
Flag for Philippines asked on

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

creating sql parameters even the sql command text has no parameter added
Visual Basic.NET

Avatar of undefined
Last Comment
systan

8/22/2022 - Mon
SOLUTION
Paul Jackson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Nasir Razzaq

Without looking at the code, I would say no.

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

ASKER
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.
Nasir Razzaq

>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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
systan

ASKER
>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.
SOLUTION
Nasir Razzaq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
systan

ASKER
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
adriankohws

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
systan

ASKER
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
Nasir Razzaq

How many SQL statements are you going to produce? Is it a lot?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
systan

ASKER
I have exactly what I wanted
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes