I need help with an SQL INSERT statement please?

[jaysoul]

Hi,

I just need to know the correct syntax for an SQL INSERT statement I am trying to get right.

I think the problematic part is when I am trying to write my VALUES.

I want to insert three variables that I have declared into three entries in my table.

Here is my statement:

SQLaddmodule = "INSERT INTO Module " & "(ModuleCode, ModuleName, Credits)" & "VALUES (variable1, variable2, variable3,)"

Can anyone help me please as the statement does not work as it is.

Kind Regards

Jay

 

[TechGnome]

SQL knows nothing about your variables... so to use it the way you have doesn't work.

SQLaddmodule = "INSERT INTO Module (ModuleCode, ModuleName, Credits) VALUES ('" & variable1 & "' ,'" & variable2 & "' ,'" & variable3 & ")"

-tg

 

[jaysoul]

Hi Techgnome.

Thanks for the reply.

I have tried it like you said I think, bit am getting this result.

 

[TechGnome]

I can tell by the colours and by looking at the end of the statement there.... it's botched... The & should NOT be infront of the VALUES.... and you are missing one after credits.... copy exactly what I posted and change the variable names.

-tg

 

[jaysoul]

I'm really sorry to be a pain in the ass! ... but

My SQL Insert statement now seems to be fine, I have copied and pasted your example in. However, my programme is still falling over when I try to execute the insert. Can you see anything else wrong my friend?

It tells me that it fails on the executenonquery part???

Many thanks

Jay

 

[TechGnome]

1) There's no need to PM for every post... I visit back often, and check up on threads. So I'll see if there's been a new post.

2) What's the error it gives?

3) What data are you trying to insert?

4) Have a look at these ADO.NET articles, they may be of some use. Especialy the second one where it shows how to use parameterized queries (I suspect that your data may be causing problems - SQL statements that are concatenated like that are notorious for causing problems.
http://www.developerkb.com/modules/wfsection/article.php?articleid=52
http://www.developerkb.com/modules/wfsection/article.php?articleid=58

-tg

 

[jaysoul]

Hey,

The error I am getting is this: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

I am just trying to put text from three text boxes into those colomns in the table of the database.

Peace

Jay

 

[TechGnome]

        Try
            myCommand.ExecuteNonQuery()
        Catch exOLEDB As OleDb.OleDbException
            MessageBox.Show(exOLEDB.ToString)
        End Try

The MessageBox should show you the exact error you are getting.

-tg

 

[jaysoul]

This is the error message I got when I inserted the message box you gave me:

 

[ManicCW]

you are doing something wrong with your strings or some column does not have a proper data type. try something like this (change to suite your needs):

Add this code to button click event:

[SIZE=2]cn = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection(ConfigurationSettings.AppSettings("DatabaseString").ToString)
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand
cmd.Connection = cn
cmd.CommandText = "INSERT INTO Categories (Category, Parent, Autor) VALUES (?, ?, ?)"
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("Category", OleDbType.VarChar))
cmd.Parameters("Category").Value = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtCategory.Text.Trim
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("Parent", OleDbType.Integer))
cmd.Parameters("Parent").Value = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cmbCategory.SelectedValue
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("Autor", OleDbType.Integer))
cmd.Parameters("Autor").Value = Request.Cookies("ManicCMS").Item("Autor").ToString
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
[/SIZE]

Notice that this is for access database, for sql server you need to change it a bit.