INSERT Problem with string

[johnuk4]

Hi (again) sorry to be a pain if uv already answered one of my questions. Iv got an insert sqlstring which when executed give an error stating there is something wrong with the statement:

SQLStr4 = "INSERT INTO Hardware_Problems Set Hardware_Id='" & HardwareId & "', Staff_Reported='" & Staff & "', Problem_Description='" & Description & "', Location='" & Location & "'"

It looks ok to me but iv prob missed something silly out]

thanks in advance

johnuk4

 

[vis781]

Youve got two types of action query all in one there. The INSERT INTO bit id right but the SET bit is used in an UPDATE command. An INSERT Command should look like this...

INSERT INTO TableName VALUES 'The Values You Want To Insert'

 

[Schenz]

It is also recommended that you use parameters rather than building a SQL statement dynamically as you are.

[SIZE=2]SQLStr4 = "INSERT INTO Hardware_Problems (Hardware_Id, Staff_Reported, Problem_Description, Location) VALUES (@[/SIZE][SIZE=2]Hardware_Id, @Staff_Reported, @Problem_Description, @Location[/SIZE][SIZE=2])

Dim sqlCommand as New SqlCommand

sqlCommand.Connection = {valid Connection}
sqlCommand.CommandText = [/SIZE][SIZE=2]SQLStr4

sqlCommand.Parameters.Add("[/SIZE][SIZE=2]@[/SIZE][SIZE=2]Hardware_Id[/SIZE][SIZE=2]", SqlDbType.VarChar).Value = [/SIZE][SIZE=2]HardwareId[/SIZE]
[SIZE=2]sqlCommand[/SIZE][SIZE=2].Parameters.Add("[/SIZE][SIZE=2]@Staff_Reported[/SIZE][SIZE=2]", SqlDbType.VarChar).Value = Staff
[/SIZE][SIZE=2]sqlCommand[/SIZE][SIZE=2].Parameters.Add("[/SIZE][SIZE=2]@Problem_Description[/SIZE][SIZE=2]", SqlDbType.VarChar).Value = Description
[/SIZE][SIZE=2]sqlCommand[/SIZE][SIZE=2].Parameters.Add("[/SIZE][SIZE=2]@Location[/SIZE][SIZE=2]", SqlDbType.VarChar).Value = Location

[/SIZE]

 

[johnuk4]

Hi, thanks for the advise. A problem i am having is that i need to enter a value for the Hardware_Problem_Id which is set to autonumber in the access database. Am i right in thinking that you use null?
Also could you tell me how you would include a time and date stamp in the string, here is my attempt so far

SQLStr4 = "INSERT INTO Hardware_Problems (Hardware_Problem_Id, Hardware_Id, Date_Reported, Time_Reported, Staff_Reported, Problem_Description, Location, Resolve_Flag) VALUES (NULL, @Hardware_Id, '10/02/2006', '10:21', @Staff_Reported, @Problem_Description, @Location, 'Yes')"
Dim sqlCommand AsNew System.Data.OleDb.OleDbCommand
sqlCommand.Connection = DBCon
sqlCommand.CommandText = SQLStr4
sqlCommand.Parameters.Add("@Hardware_Id", SqlDbType.VarChar).Value = HardwareId
sqlCommand.Parameters.Add("@Staff_Reported", SqlDbType.VarChar).Value = Staff
sqlCommand.Parameters.Add("@Problem_Description", SqlDbType.VarChar).Value = Description
sqlCommand.Parameters.Add("@Location", SqlDbType.VarChar).Value = Location

I keep on getting the error: Error reading database, No value given for one or more required parameters

thanks again

john

 

[TechGnome]

Nooo.... just don't include it in the insert list....

SQLStr4 = "INSERT INTO Hardware_Problems (Hardware_Id, Date_Reported, Time_Reported, Staff_Reported, Problem_Description, Location, Resolve_Flag) VALUES (@Hardware_Id, '10/02/2006', '10:21', @Staff_Reported, @Problem_Description, @Location, 'Yes')"
Dim sqlCommand AsNew System.Data.OleDb.OleDbCommand
sqlCommand.Connection = DBCon
sqlCommand.CommandText = SQLStr4
sqlCommand.Parameters.Add("@Hardware_Id", SqlDbType.VarChar).Value = HardwareId
sqlCommand.Parameters.Add("@Staff_Reported", SqlDbType.VarChar).Value = Staff
sqlCommand.Parameters.Add("@Problem_Description", SqlDbType.VarChar).Value = Description
sqlCommand.Parameters.Add("@Location", SqlDbType.VarChar).Value = Location

Like that...

-tg

 

[johnuk4]

Still seem to be having the same problem unfortunetly, Here is the code im using to insert however it keeps giving me the error:

Error reading database, No value given for one or more required parameters. Iv checked the db and none of the fields are set to required. Sorry to be a pain just not very experienced at programming.

thanks a lot

john

Private Sub btnLog_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLog.Click
Dim HardwareId As String
Dim Staff As String
Dim Description As String
Dim Location As String
HardwareId = ListId.Text
Staff = txtStaff.Text
Description = txtDescription.Text
Location = txtLocation.Text
Try
DBCon.Open()
Dim sa As Integer
Dim DSet4 As New DataSet, SQLStr4 As String
Dim cmd4 As System.Data.OleDb.OleDbCommand
Dim dbAdaptr4 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
With dbAdaptr4
.TableMappings.Add("Table", "Hardware_Problems")
SQLStr4 = "INSERT INTO Hardware_Problems (Hardware_Id ,Staff_Reported, Problem_Description, Location) VALUES (@Hardware_Id, @Staff_Reported, @Problem_Description, @Location)"
Dim sqlCommand As New System.Data.OleDb.OleDbCommand
sqlCommand.Connection = DBCon
sqlCommand.CommandText = SQLStr4
sqlCommand.Parameters.Add("@Hardware_Id", SqlDbType.VarChar).Value = HardwareId
sqlCommand.Parameters.Add("@Staff_Reported", SqlDbType.VarChar).Value = Staff
sqlCommand.Parameters.Add("@Problem_Description", SqlDbType.VarChar).Value = Description
sqlCommand.Parameters.Add("@Location", SqlDbType.VarChar).Value = Location

cmd4 = New System.Data.OleDb.OleDbCommand(SQLStr4, DBCon)
sa = cmd4.ExecuteNonQuery()
End With
DBCon.Close()
Catch ex As Exception
Label1.Text = "Error reading database " & ex.Message
End Try
End Sub

 

[Schenz]

The Command you are trying to run (cmd4) does not have the parameters set. sqlCommand Does. Take the cmd4 stuff out, and then change

[SIZE=2] sa = cmd4.ExecuteNonQuery()
[/SIZE]

to

[SIZE=2] sa = sqlCommand.ExecuteNonQuery()
[/SIZE]

 

[johnuk4]

Thanks very much for the help its finally worked