gettinig insert error and need help
-
Protected Sub btnCommit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCommit.Click Dim ra As Integer Dim MyConn As New System.Data.OleDb.OleDbConnection(Connstring) MyConn.Open() ' open connection Dim MySql As String = "Insert into ExpenseReport _ (DateRow1,WhomRow1,CCardAmountRow1,PCardAmountRow1,CashAmount,VehicleNumRow1,ProjectnameRow1, _ TypeofExpenseRow1) values " & tbxDate.Text.ToString & "," & tbxWhom.Text.ToString & "," & _ tbxCCard.Text.ToString & "," & tbxPCard.Text & "," & tbxCash.Text & "," & tbxVehicle.Text & "," & _ tbxProject.Text.ToString & "," & ddnExpense.SelectedValue.ToString Dim command As New System.Data.OleDb.OleDbCommand(MySql, MyConn) ra = command.ExecuteNonQuery() MyConn.Dispose() Response.Write("New Row inserted" & ra) End Sub End Class Can someone tell me why I'm getting a insertion syntax error when trying to run this code. My db has those exact fields. This is an access db by the way if that matters. thanks for the help Win32newb "Programming is like Sex, make a mistake and you end up providing support for a long time"
-
Protected Sub btnCommit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCommit.Click Dim ra As Integer Dim MyConn As New System.Data.OleDb.OleDbConnection(Connstring) MyConn.Open() ' open connection Dim MySql As String = "Insert into ExpenseReport _ (DateRow1,WhomRow1,CCardAmountRow1,PCardAmountRow1,CashAmount,VehicleNumRow1,ProjectnameRow1, _ TypeofExpenseRow1) values " & tbxDate.Text.ToString & "," & tbxWhom.Text.ToString & "," & _ tbxCCard.Text.ToString & "," & tbxPCard.Text & "," & tbxCash.Text & "," & tbxVehicle.Text & "," & _ tbxProject.Text.ToString & "," & ddnExpense.SelectedValue.ToString Dim command As New System.Data.OleDb.OleDbCommand(MySql, MyConn) ra = command.ExecuteNonQuery() MyConn.Dispose() Response.Write("New Row inserted" & ra) End Sub End Class Can someone tell me why I'm getting a insertion syntax error when trying to run this code. My db has those exact fields. This is an access db by the way if that matters. thanks for the help Win32newb "Programming is like Sex, make a mistake and you end up providing support for a long time"
try this.... in ur code page in the top of the page wrie Imports System.Data.oledb like this...
Imports System.Data.oledb Public Class Form1 Inherits System.Windows.Forms.Formand in ur buttonProtected Sub btnCommit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCommit.Click Dim MySql As as new oledbcommand mysql.Connection = OleDbConnection1 oledbconnection1.Open() ' open connection my sql.commandtext = "Insert into ExpenseReport _ (DateRow1,WhomRow1,CCardAmountRow1,PCardAmountRow1,CashAmount,VehicleNumRow1,ProjectnameRow1, _ TypeofExpenseRow1) values( " tbxDate.Text.ToString & "," & tbxWhom.Text.ToString & "," & _ tbxCCard.Text.ToString & "," & tbxPCard.Text & "," & tbxCash.Text & "," & tbxVehicle.Text & "," & _ tbxProject.Text.ToString & "," & ddnExpense.SelectedValue.ToString) mysql.ExecuteNonQuery() oledbconnection1.close() End Sub End Classthat if u create ur connectin by the wizard and as i know the wizard allready will give u name of ur connection as (oledbconnection1) i hope that will work:) rusashksa@yahoo.com Sally<<<>>>tAkE mE 2 uR pAraDiSe<<<>>>Sally -
try this.... in ur code page in the top of the page wrie Imports System.Data.oledb like this...
Imports System.Data.oledb Public Class Form1 Inherits System.Windows.Forms.Formand in ur buttonProtected Sub btnCommit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCommit.Click Dim MySql As as new oledbcommand mysql.Connection = OleDbConnection1 oledbconnection1.Open() ' open connection my sql.commandtext = "Insert into ExpenseReport _ (DateRow1,WhomRow1,CCardAmountRow1,PCardAmountRow1,CashAmount,VehicleNumRow1,ProjectnameRow1, _ TypeofExpenseRow1) values( " tbxDate.Text.ToString & "," & tbxWhom.Text.ToString & "," & _ tbxCCard.Text.ToString & "," & tbxPCard.Text & "," & tbxCash.Text & "," & tbxVehicle.Text & "," & _ tbxProject.Text.ToString & "," & ddnExpense.SelectedValue.ToString) mysql.ExecuteNonQuery() oledbconnection1.close() End Sub End Classthat if u create ur connectin by the wizard and as i know the wizard allready will give u name of ur connection as (oledbconnection1) i hope that will work:) rusashksa@yahoo.com Sally<<<>>>tAkE mE 2 uR pAraDiSe<<<>>>SallyNo offense but I see no difference in the code you posted than that of mine. ONly things is your making it a shortcut by using Imports System.Data.oledb and your making a Oledbconnection which is what I'm doing in my code the connection string is write all that works the issue is the insert string. I use this same syntax with other dataaccess the only problem is the insert string its formated wrong and i'm not sure what i'm doing wrong. Win32newb "Programming is like Sex, make a mistake and you end up providing support for a long time"
-
Protected Sub btnCommit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCommit.Click Dim ra As Integer Dim MyConn As New System.Data.OleDb.OleDbConnection(Connstring) MyConn.Open() ' open connection Dim MySql As String = "Insert into ExpenseReport _ (DateRow1,WhomRow1,CCardAmountRow1,PCardAmountRow1,CashAmount,VehicleNumRow1,ProjectnameRow1, _ TypeofExpenseRow1) values " & tbxDate.Text.ToString & "," & tbxWhom.Text.ToString & "," & _ tbxCCard.Text.ToString & "," & tbxPCard.Text & "," & tbxCash.Text & "," & tbxVehicle.Text & "," & _ tbxProject.Text.ToString & "," & ddnExpense.SelectedValue.ToString Dim command As New System.Data.OleDb.OleDbCommand(MySql, MyConn) ra = command.ExecuteNonQuery() MyConn.Dispose() Response.Write("New Row inserted" & ra) End Sub End Class Can someone tell me why I'm getting a insertion syntax error when trying to run this code. My db has those exact fields. This is an access db by the way if that matters. thanks for the help Win32newb "Programming is like Sex, make a mistake and you end up providing support for a long time"
MeterMan wrote:
Dim MySql As String = "Insert into ExpenseReport _ (DateRow1,WhomRow1,CCardAmountRow1,PCardAmountRow1,CashAmount,VehicleNumRow1,ProjectnameRow1, _ TypeofExpenseRow1) values " & tbxDate.Text.ToString & "," & tbxWhom.Text.ToString & "," & _ tbxCCard.Text.ToString & "," & tbxPCard.Text & "," & tbxCash.Text & "," & tbxVehicle.Text & "," & _ tbxProject.Text.ToString & "," & ddnExpense.SelectedValue.ToString
I've seen this WAY too many times... Junk like this just leads to problems that are difficult to find and opens you up to Injection attacks. You can avoid this by using Parameterized Queries instead. Search the articles for "Sql Injection Attacks" and you'll find a great article by Colin Angus Mackay, which will detail how to do this. Anyway, you're missing some quote marks, concantenation characters (&), parenthesisin the SQL statements, ..., and it just bugs the #&@% out of me to see people calling
.ToString()on aTextproperty.Textreturns a String, so why call.ToString()on a String?!?!?Dim MySql As String = "Insert into ExpenseReport " & _
"(DateRow1, WhomRow1, CCardAmountRow1, PCardAmountRow1, " & _
"CashAmount, VehicleNumRow1, ProjectnameRow1, TypeofExpenseRow1)" & _
" values (" & tbxDate.Text & "," & tbxWhom.Text & "," & _
tbxCCard.Text & "," & tbxPCard.Text & "," & tbxCash.Text & "," & _
tbxVehicle.Text & "," & tbxProject.Text & "," & _
ddnExpense.SelectedValue.ToString() & ")"RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
MeterMan wrote:
Dim MySql As String = "Insert into ExpenseReport _ (DateRow1,WhomRow1,CCardAmountRow1,PCardAmountRow1,CashAmount,VehicleNumRow1,ProjectnameRow1, _ TypeofExpenseRow1) values " & tbxDate.Text.ToString & "," & tbxWhom.Text.ToString & "," & _ tbxCCard.Text.ToString & "," & tbxPCard.Text & "," & tbxCash.Text & "," & tbxVehicle.Text & "," & _ tbxProject.Text.ToString & "," & ddnExpense.SelectedValue.ToString
I've seen this WAY too many times... Junk like this just leads to problems that are difficult to find and opens you up to Injection attacks. You can avoid this by using Parameterized Queries instead. Search the articles for "Sql Injection Attacks" and you'll find a great article by Colin Angus Mackay, which will detail how to do this. Anyway, you're missing some quote marks, concantenation characters (&), parenthesisin the SQL statements, ..., and it just bugs the #&@% out of me to see people calling
.ToString()on aTextproperty.Textreturns a String, so why call.ToString()on a String?!?!?Dim MySql As String = "Insert into ExpenseReport " & _
"(DateRow1, WhomRow1, CCardAmountRow1, PCardAmountRow1, " & _
"CashAmount, VehicleNumRow1, ProjectnameRow1, TypeofExpenseRow1)" & _
" values (" & tbxDate.Text & "," & tbxWhom.Text & "," & _
tbxCCard.Text & "," & tbxPCard.Text & "," & tbxCash.Text & "," & _
tbxVehicle.Text & "," & tbxProject.Text & "," & _
ddnExpense.SelectedValue.ToString() & ")"RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
Thanks for the reply and also the word of warning on injects I will read that. One question. How do you make the page accept the changed values n a dropdownbox only when you hit commit button. Basically ddnExpense.SelectValue is changed and the other values are put into the text boxes but the pages has no idea what the value of ddnExpense is because it hasn't postedback. The thing is I don't want it to post back when i make a change in the dropdown because then Id loose all the data in my other fields. Any help on this would be great. Win32newb "Programming is like Sex, make a mistake and you end up providing support for a long time"