Error in SQL statement

P

PHTech

Senior Member
Joined
Aug 21, 2006
Messages
588
Reaction score
0
Location
Witbank
Hi there... Below is code for my SQL statement which gives me an error:

Code: 
<script runat="server">
    
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        If Page.IsPostBack = False Then
            Label1.Visible = False
        End If
    End Sub
    
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim Connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=C:\Inetpub\wwwroot\XCSA_Intranet_NewServer\assets\database\3GAdmin.mdb")
        Connection.Open()
        Dim Command As New OleDbCommand("INSERT INTO MainInfo(SIMTelNo," & _
        "Location, ContractDescription, DeviceType, Name, Surname, Position, FOB, SerialNo, IMEI, Comments, SIMPIN, SIMPUK)VALUES(@SIMTelNo, @Location, @ContractDescription, @DeviceType, @Name, @Surname, @Position, @FOB, @SerialNo, @IMEI, @Comments, @SIMPIN, @SIMPUK)", Connection)
        Command.Parameters.Add(New OleDbParameter("@SIMTelNo", TextBox1.Text))
        Command.Parameters.Add(New OleDbParameter("@Location", DropDownList1.Text))
        Command.Parameters.Add(New OleDbParameter("@ContractDescription", DropDownList2.Text))
        Command.Parameters.Add(New OleDbParameter("@DeviceType", DropDownList3.Text))
        Command.Parameters.Add(New OleDbParameter("@Name", TextBox2.Text))
        Command.Parameters.Add(New OleDbParameter("@Surname", TextBox3.Text))
        Command.Parameters.Add(New OleDbParameter("@Position", TextBox4.Text))
        Command.Parameters.Add(New OleDbParameter("@FOB", TextBox5.Text))
        Command.Parameters.Add(New OleDbParameter("@SerialNo", TextBox6.Text))
        Command.Parameters.Add(New OleDbParameter("@IMEI", TextBox7.Text))
        Command.Parameters.Add(New OleDbParameter("@Comments", TextBox8.Text))
        Command.Parameters.Add(New OleDbParameter("@SIMPIN", TextBox9.Text))
        Command.Parameters.Add(New OleDbParameter("@SIMPUK", TextBox10.Text))
        Command.ExecuteNonQuery()

        Connection.Close()
        
        Label1.Text = "Record inserted."
        Label1.Visible = True
        
        TextBox1.Text = ""
        DropDownList1.Text = ""
        DropDownList2.Text = ""
        DropDownList3.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
        TextBox7.Text = ""
        TextBox8.Text = ""
        TextBox9.Text = ""
        TextBox10.Text = ""
    End Sub
    
 </script>

When executing the whole thing in my ASP.net page, I get the following error:

Server Error in '/XCSA_Intranet_NewServer' Application.
--------------------------------------------------------------------------------

Syntax error in INSERT INTO statement.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

Source Error:


Line 30: Command.Parameters.Add(New OleDbParameter("@SIMPIN", TextBox9.Text))
Line 31: Command.Parameters.Add(New OleDbParameter("@SIMPUK", TextBox10.Text))
Line 32: Command.ExecuteNonQuery()
Line 33:
Line 34: Connection.Close()


Source File: c:\inetpub\wwwroot\xcsa_intranet_newserver\pages\12_administration\3g_statistics\pages\database\add_3g_users2.aspx Line: 32
Click to expand...

Any suggestions...?
 
Not sure but I do my Parameter declarations differently, so it could be that

Syntax error means there's something wrong with the physical INSERT INTO statement. Maybe try and use the debug step through to check what the statement is before it tries to execute it

also:

[ame]http://www.amazon.co.uk/ASP-NET-Dummies-Bill-Hatfield/dp/0764508660[/ame]
 
SIMPUK)VALUES

There is no space between the ) and VALUES, try that?.....
 
This also didn't fixed the problem...

I don't know where I have done something wrong, because I can't seem to find anything wrong in the syntax...
 
Shred said:
SIMPUK)VALUES

There is no space between the ) and VALUES, try that?.....
Click to expand...

If he didn't have the )VALUES( then I'd also think that

Shred said:
Ja, it could be that. What database are you connecting to?
Click to expand...

Can't be that, SQL has that as a default parameter (as well as password) but it shouldn't hinder him using them in statements like that if there are corresponding fields.

PHTech,

Did you try and step through debug it and get the SQL statement before it tries to execute yet? Or did you just simply ignore my advice to help teach you simple debugging methods to learn how to do these things properly and not post random questions online?
 
PHTech, you will run into these issues a lot as a developer, so it will be worth a lot if you can step through your code and get the actual SQL statement and find the syntax error.
 
Thanx for the replies...

I have put the "Position" field in [] brackets. That seemed to fixed the problem for some freaky reason... The "Name" field is still not in brackets [] and doesn't give errors, but only the "Position" field...
 
PHTech said:
Thanx for the replies...

I have put the "Position" field in [] brackets. That seemed to fixed the problem for some freaky reason... The "Name" field is still not in brackets [] and doesn't give errors, but only the "Position" field...
Click to expand...

Yeh - depends on the database.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X