Problems inserting and Updating an Access DB

[Jouster]

Hi,

I am going through a tutorial on ASP.Net ( used ASP for a long time) and I can't figure out why I keep getting an error when I try to update or insert a record.

The table is called Models and the fields are:

Model - String
BasePrice - String
Catalog - String
Default_PN - String

I made all of the fields string to make things easier at this time.

Here is the error and the printed out SQL statement:

Syntax error in INSERT INTO statement. SQL= INSERT INTO Models (Model, BasePrice, Catalog, Default_PN) VALUES ( '1847','2295','1847','1847-0000000000000')

An here is the ASP code:


sub Submit(Sender as Object, e as EventArgs)
dim i, j as integer
dim params(7) as string
dim strText as string
dim blnGo as boolean = true

j=0

for i = 0 to AddPanel.Controls.Count -1
if AddPanel.controls(i).GetType IS GetType(TextBox) then
strText = Ctype(AddPanel.Controls(i), TextBox).Text
if strText <>"" then
params(j) = strText
else
blnGo=false
lblMessage.Text = lblMessage.Text & _
"You forgot to enter a value for " & _
AddPanel.Controls(i).ID & "<p>"
lblMessage.Style("Forecolor")="Red"
end if
j = j +1
end if
next


if not blnGo then
exit sub
end if

dim strSql as string = "INSERT INTO Models (Model, BasePrice, Catalog, Default_PN)" & _
" VALUES ( '" & params(0) & "','" & params(1) & "','" & params(2) & "','" & params(3) & "')"

lblMessage2.Text = strSql
ExecuteStatement(strSql)

FillDataGrid()

end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand(strSQL, objConn)

try
objCmd.Connection.Open()
objCmd.ExecuteNonQuery()
catch ex as FormatException
lblMessage.Text = ex.Message & " " & strSQL
catch ex as OleDbException
lblMessage.Text = ex.Message & VBCRLF & "SQL= " & strSQL
catch ex as Exception
lblMessage.Text = ex.Message & strSQL
end try

objCmd.Connection.Close()

end function

I set the permissions on the database to everyone full control to take that out of the picture.

Any help would be appreciated.

 

[ca8msm]

Syntax error in INSERT INTO statement. SQL= INSERT INTO Models (Model, BasePrice, Catalog, Default_PN) VALUES ( '1847','2295','1847','1847-0000000000000')

Copy that piece of SQL out and run it directly in Access. Does it work?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[Jouster]

It works from a classic ASP page just as it is.
So I figured it must be an ASP.Net problem even though it says the SQL statement is wrong...?????

 

[ca8msm]

That's one of the reasons I asked to see if it works in Access or not. MS Access is generally very poor at giving error messages and doesn't work well in web environments - you have better alternatives so I'd go with something like SQL Server Express.

If you decide to stick with access, it could very well be a permissions error so make sure the ASPNET or NETWORK SERVICE (for Windows 2003) account has the appropriate permissions.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[jmeckley]

dim strSql as string = "INSERT INTO Models (Model, BasePrice, Catalog, Default_PN)" & _
" VALUES ( '" & params(0) & "','" & params(1) & "','" & params(2) & "','" & params(3) & "')"

this is also problem... sql injection

use Command Parameters instead.

string sql = "insert into models (Model, BasePrice, Catalog, Default_PN) values (@a, @b, @c, @d)"

OleDbCommand cmd = new OldDbCommand(sql, new OleDbConnection());
cmd.Parameters.Add("a", [DataType]).Value = params(0);
cmd.Parameters.Add("b", [DataType]).Value = params(1);
cmd.Parameters.Add("c", [DataType]).Value = params(2);
cmd.Parameters.Add("d", [DataType]).Value = params(3);

cmd.ExecuteNonQuery();

it would be wiser to pass the parameters as a specific type. either primative, or custom dtos

string model;
int basePrice;
bool catalog;
DataTime defaultPN;
//or
public call ModelDTO
{
   private string model;
   private int basePrice;
   private bool catalog;
   private DataTime defaultPN; 

   public string Model
   {
      get { return this.model; }
      get { this.model = value; }
   }
   public int BasePrice
   {
      get { return this.basePrice; }
      get { this.basePrice= value; }
   }
   public bool Catalog
   {
      get { return this.catalog; }
      get { this.catalog= value; }
   }
   public DataTime DefaultPN 
   {
      get { return this.defaultPN; }
      get { this.defaultPN= value; }
   }

   public ModelDTO() { }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Jouster]

Thanks for the info. As I stated I was doing a tutorial and they didn't get to the parameters section yet and I was stuck here. I am going to try to get SQL Express running and use that so i don't waste more time on Access wierd behaviors.