insert data it give error

[sadia644]

can anyone tell me OLEDB connection i creat connection but when i try to insert data it give error on nonQuery i m using Visual studio 2008...
insert,update,delete and i want to retrive data
can any one provide me helping material books,note etc related to this topic
thanks

 

[Hack]

Show me what you have and I'll show you how to correct it.

 

[sadia644]

data Insert prob

mports System.Data
Imports System.Data.OleDb

Public Class Form1
Dim DBConnect As OleDb.OleDbConnection
Dim DBAdapter As OleDb.OleDbDataAdapter
Dim DBReader As OleDb.OleDbDataReader
Dim SqlStr As String

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
DBConnect = New OleDbConnection("Provider=SQLOLEDB;Data Source=A-43A7F057CAD34;Integrated Security=SSPI;Initial Catalog=Salary")
DBConnect.Open()
SqlStr = "insert into salary value ('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" & Trim(TextBox3.Text) & "','" & Trim(TextBox4.Text) & ")"
DBAdapter.InsertCommand = New OleDb.OleDbCommand(SqlStr, DBConnect)
DBAdapter.InsertCommand.ExecuteNonQuery()
DBConnect.Close()
MsgBox("Record Inserted")


End Sub


I m trying to learn how data can insert, deleted,updated and retrieve......
but i can not go forward from insert...
plz tell wt is error in it
thanks

 

[Hack]

OLEDB is used to connect to an Access database. Based on your connection string, you are connection to a SQL Server database. Try this instead

Imports System.Data.SqlClient
'first connect to your database

Private myConnection As SqlConnection

Private Sub OpenDB()
Dim yourSqlconnection As New SqlConnection("your_sql_connection_here") 'Replace with your sqlConnection 

myConnection = New SqlConnection("Provider=SQLOLEDB;Data Source=A-43A7F057CAD34;
Integrated Security=SSPI;Initial Catalog=Salary")

myConnection.Open()
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As 
System.EventArgs) Handles MyBase.Load
 OpenDB()
End Sub

Private Sub btnAddNewRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) 
Handles btnAddNewRecord.Click

SqlStr = "insert into salary value ('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" 
& Trim(TextBox3.Text) & "','" & Trim(TextBox4.Text) 
& ")"

Dim command As New SqlCommand(SqlStr, myConnection)
command.ExecuteNonQuery()
End Sub

 

[sadia644]

mports System.Data.SqlClient

Public Class Form1
Private myConnection As SqlConnection
Dim SqlStr As String
Private Sub OpenDB()
Dim yourSqlconnection As New SqlConnection 'Replace with your sqlConnection
myConnection = New SqlConnection("Data Source=A-43A7F057CAD34;Initial Catalog=Salary;Integrated Security=True")
myConnection.Open()
End Sub

rivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
SqlStr = "insert into salary value ('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" & Trim(TextBox3.Text) & "','" & Trim(TextBox4.Text) & ")"

Dim command As New SqlCommand(SqlStr, myConnection)
command.ExecuteNonQuery()
MsgBox("Record Inserted")


same error Incorrect syntax near 'value'. Unclosed quotation mark after the character string '100)'.


now did i write correct...?

 

[sadia644]

mports System.Data.SqlClient

Public Class Form1
Private myConnection As SqlConnection
Dim SqlStr As String
Private Sub OpenDB()
Dim yourSqlconnection As New SqlConnection 'Replace with your sqlConnection
myConnection = New SqlConnection("Data Source=A-43A7F057CAD34;Initial Catalog=Salary;Integrated Security=True")
myConnection.Open()
End Sub

rivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
SqlStr = "insert into salary value ('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" & Trim(TextBox3.Text) & "','" & Trim(TextBox4.Text) & ")"

Dim command As New SqlCommand(SqlStr, myConnection)
command.ExecuteNonQuery()
MsgBox("Record Inserted")

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
OpenDB()

End Sub


same error Incorrect syntax near 'value'. Unclosed quotation mark after the character string '100)'.


now did i write correct...?

 

[Hack]

Two things:

1. You are missing a single quote in your closing paraenthese

& ")"
'should be
& "')"

2. Also, the keyword is VALUES not VALUE

 

[sadia644]

Thanks u so much..........
now i m tire but i hope it will work thanks a lot

 

[Hack]

If it doesn't work, come back and let us know what happened.

 

[sadia644]

Thanks ya now its works......
but one question arrises in mind that u open connection DBopen() but why u did not close connection DBclose()?
it mean connection will remain open?

 

[InertiaM]

Whilst I dont believe it is essential, it would be better to give the field names as well as the values. It means your values are guaranteed to go into the correct fields - eg

SqlStr = "insert into salary (FIELDNAME1, FIELDNAME2, FIELDNAME3, FIELDNAME4) values ('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" & Trim(TextBox3.Text) & "','" & Trim(TextBox4.Text) & ")"

You should also look at parameterised queries - they will make your queries far easier to read and debug. See the link in my signature below. If it was written with parameters, your query would have been :-

SqlStr = "insert into salary (FIELDNAME1, FIELDNAME2, FIELDNAME3, FIELDNAME4) values (@FIELD1, @FIELD2, @FIELD3, @FIELD4)"

 

[sadia644]

now i want help on update, delete and retrieve data did anyone have helping material...

 

[InertiaM]

Let me google that for you

 

[sadia644]

"insert into Salary (Emp_Id, Emp_Name, Emp_Department, Emp_Salary) VALUES (@Emp_Id, @Emp_Name, @Emp_Department, @Emp_Salary)"

i try to use 2nd method
it give error
Must declare the scalar variable "@Emp_Id"

 

[InertiaM]

it give error
Must declare the scalar variable "@Emp_Id"

Did you declare the parameter? Did you allocate the parameters to the command?