sql insert not saving data (HOW TO INSERT WITHOUT USING PARAMETER)

systan
systan used Ask the Experts™
on
The sample1 result of that is:
Dim NScript As String = "insert into expences(dtime,expencefor,cash,byemp) values ('Jan 2012','BILLS','38.50','JOHN')"
sample1:
Dim NScript As String = "insert into expences(dtime,expencefor,cash,byemp) values (" & "'" & dtnow.ToString & "'" & "," & "'" & txtexp & "'" & "," & "'" & txtcash & "'" & "," & "'" & Txtexpences.Tag.ToString & "'" & ")"

Open in new window


The sample2 result is this:(withou single quatation)
Dim NScript As String = "insert into expences(dtime,expencefor,cash,byemp) values (Jan 2012,BILLS,38.50,JOHN)"
sample2:(withou single quatation)
        
Dim NScript As String = "insert into expences(dtime,expencefor,cash,byemp) values (" & dtnow.ToString & "," & txtexp & "," & txtcash & "," & Txtexpences.Tag.ToString & ")"

Open in new window



The above is for saving record from tcpClient network, but it doesn't insert.
Where's the correct script?
I'm using this function, whats wrong with it?
usage:
SaveSendReceived(Nscript, "Network")
    
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
            '    MessageBox.Show(value(x).ToString)
            '    i += 1
            'Next

''//DISABLED BECAUSE IT DOESN'T SAVED ANYTHING
            'sc.Parameters.Add(New SQLiteParameter("dtime", value(0)))
            'sc.Parameters.Add(New SQLiteParameter("expencefor", value(1)))
            'sc.Parameters.Add(New SQLiteParameter("cash", Convert.ToDecimal(value(2))))
            'sc.Parameters.Add(New SQLiteParameter("byemp", value(3)))

            '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")
        End If
        sc.ExecuteNonQuery()
        sc.Parameters.Clear()
        sc.Dispose()
    End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rose BabuSenior Team Manager
Commented:
Hi,

You already formatted the SQL command as

Dim NScript As String = "insert into expences(dtime,expencefor,cash,byemp) values (" & "'" & dtnow.ToString & "'" & "," & "'" & txtexp & "'" & "," & "'" & txtcash & "'" & "," & "'" & Txtexpences.Tag.ToString & "'" & ")"

Open in new window

Then you are passing this sql string to a function, and why you are adding parameters to the sc command?

Try to execute the command alone as like below. show me any error occurs

Try

	SaveSendReceivedExpences(NScript, "Network")
	
Catch ex As Exception

    MessageBox.Show(ex.Message)

End Try
    
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
		' Some code part 
		
		MessageBox.Show("SAVING TO LOCAL SERVER")
	Else
		' Some code part 
		
		MessageBox.Show("SAVING FROM NETWORK")
	End If
	sc.ExecuteNonQuery()
	sc.Parameters.Clear()
	sc.Dispose()
End Sub

Open in new window

else are you calling any stored procedure?
Commented:
else are you calling any stored procedure?
No,
Then you are passing this sql string to a function, and why you are adding parameters to the sc command?
I'm only adding a parameter if the culture is "local"
if culture.Equals("local") then
''//with parameters here
else
Without Parameters Here(if ok, because values are already given in the script)
Endif

You already formatted the SQL command as
Yes,
The result is this:( with single quatation)
insert into expences(dtime,expencefor,cash,byemp) values ('Jan 2012','BILLS','38.50','JOHN')

And It can't be inserted with this function:
Dim sc As New SQLiteCommand(script, mycon)
sc.ExecuteNonQuery()
There is no error using try statement.

The only numeric is the BILLS value, So it will certainly error with BILLS string.
I'll try not using the quotation in the numeric value.
(Still no luck, it doesn't saved), Lately Wrong, Yes, it work now.
I solved it.

Now using: SaveSendReceivedExpences(1_Script_Only_for_Local_or_in_Network)
    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


thanks

Author

Commented:
I solved it, and I gave points to the assisting experts, of which I think another solution.

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