Insert Commands SQL Server

[Charles A. Lackman]

Hello,

I have written quite a few programs that use Jet4.0 databases. I just
recently updated to SQL Server and have run into a problem with sending
Insert commands.

Using OleDb this works with no problems against Jet 4.0 and dBase and other
databases, but I get an exception when i use this against SQL Server (MSDE).

The error is happening lin line Number 2:

IE: Incorrent syntax near "?"

If I don't use parameters and simply change the CommandText to:
INSERT INTO TESTING VALUES ('One', 'Two',
'Three', 'Four', 'Five')
it works with the value One thru Five in each column.

Any Ideas??

1 Dim SQLCommand As New SqlClient.SqlCommand()
2 SQLCommand.CommandText = "INSERT INTO TESTING VALUES(?,?,?,?,?)"
3 SQLCommand.Connection = SQLConnection

4 SQLCommand.Parameters.Add("@One", SqlDbType.Char)
5 SQLCommand.Parameters(0).Value = TextBox1.Text

6 SQLCommand.Parameters.Add("@Two", SqlDbType.Char)
7 SQLCommand.Parameters(1).Value = TextBox2.Text

8 SQLCommand.Parameters.Add("@Three", SqlDbType.Char)
9 SQLCommand.Parameters(2).Value = TextBox3.Text

10 SQLCommand.Parameters.Add("@Four", SqlDbType.Char)
11 SQLCommand.Parameters(3).Value = TextBox4.Text

12 SQLCommand.Parameters.Add("@Five", SqlDbType.Char)
13 SQLCommand.Parameters(4).Value = TextBox5.Text


14 Try
15 SQLConnection.Open()
16 SQLCommand.ExecuteNonQuery()
17 SQLConnection.Close()
18 Catch errr As SqlClient.SqlException
19 MessageBox.Show(errr.ToString)
20 SQLConnection.Close()
21 Catch err As Exception
22 MessageBox.Show(err.ToString)
23 SQLConnection.Close()
24 End Try

Thanks,
Chuck

 

[Elton Wang]

Hi Charles,

I don't think there is problem in line 2. The problem
might be in line 4, 6, 8, 10, and 12. It's better also to
give size when setting Parameter. It looks like

SQLCommand.Parameters.Add("@One", SqlDbType.Char, size)

The size and also Type (SqlDbType) are depending on field
in db table.

Anyway, give it try.

HTH

Elton Wang
(e-mail address removed)

 

[Charles A. Lackman]

Hello,

Thank You for your Response.

I still get the same problem when using your example. But when I modify the
code in line 2, the problem is fixed.

It seems like alot of work to make an Insert command and I do not want to
have to go through that, Update commands are enough work. Anyway, this does
not seem right to me, what are your thoughts?


1 Dim SQLCommand As New SqlClient.SqlCommand()

2 SQLCommand.CommandText = "INSERT INTO TESTING VALUES(@One, @Two,
@Three, @Four, @Five)"

3 SQLCommand.Connection = SQLConnection

4 SQLCommand.Parameters.Add("@One", SqlDbType.Char)
5 SQLCommand.Parameters(0).Value = TextBox1.Text

6 SQLCommand.Parameters.Add("@Two", SqlDbType.Char)
7 SQLCommand.Parameters(1).Value = TextBox2.Text

8 SQLCommand.Parameters.Add("@Three", SqlDbType.Char)
9 SQLCommand.Parameters(2).Value = TextBox3.Text

10 SQLCommand.Parameters.Add("@Four", SqlDbType.Char)
11 SQLCommand.Parameters(3).Value = TextBox4.Text

12 SQLCommand.Parameters.Add("@Five", SqlDbType.Char)
13 SQLCommand.Parameters(4).Value = TextBox5.Text

Thanks,

Chuck

Hi Charles,

I don't think there is problem in line 2. The problem
might be in line 4, 6, 8, 10, and 12. It's better also to
give size when setting Parameter. It looks like

SQLCommand.Parameters.Add("@One", SqlDbType.Char, size)

The size and also Type (SqlDbType) are depending on field
in db table.

Anyway, give it try.

HTH

Elton Wang
(e-mail address removed)