VB.NET – Can’t insert data into database.

255 pts.
Tags:
SQL Server 2005
VB.NET
Visual Basic .NET
I used insert command it worked successfully that ur data has updated.But it is not showing in the database Plz look in the below code and tell me where iam wrong Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Windows.Forms Imports System.Data.SqlClient Public Class SAdmission Shared WithEvents con As SqlConnection Shared Sub Main() End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Me.Hide() Home.Show() End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim adapter As SqlDataAdapter Dim cmd As SqlCommand Dim ds As New DataSet Dim test As Boolean Dim conn As SqlConnection Dim sql As String conn = New SqlConnection("Data Source=NASSQLEXPRESS;Database=data2;Integrated Security=True; Connect Timeout=30;") sql = "(Insert into SAdmission(RegistrationNo,Name,DateofBirth,Class,Nationality,MotherTongue,Religion,BloodGroup,SecondLanguage,Address1,Address2,PhoneNo,DateofJoining,FathersName,Occupation,MothersName,Occupation1)values(" 'RegistrationNo’,'Name’,'DateofBirth’,'Class’,'Nationality’,'MotherTongue’,'Religion’,'BloodGroup’,'SecondLanguage’,'Address1′,’Address2′,’PhoneNo’,'DateofJoining’,'FathersName’,'Occupation’,'MothersName’,'Occupation1′”)” conn.Open() MsgBox("conection has open") test = conn.State 'to check the connection state MsgBox(test) cmd = New SqlCommand(sql, conn) MessageBox.Show(cmd.CommandText.ToString()) adapter = New SqlDataAdapter adapter.InsertCommand = cmd conn.Close() MsgBox("You have successflly updated") End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Me.Close() End Sub End Class

Answer Wiki

Thanks. We'll let you know when a new response is added.

From <a href=”http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx”>MSDN</a>:

<i>”The SqlDataAdapter, <b>serves as a bridge between a DataSet and SQL Server for retrieving and saving data</b>. The SqlDataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source, and Update, which <b>changes the data in the data source to match the data in the DataSet</b>”</i>

It seems that you are not really using the dataset to show information to the user, or manipulate the data, and maybe you don’t really need a dataset (and the corresponding SqlDataAdapter).

You could execute your insert command (without asigning it to the InsertCommand property of an adapter) this way:

<pre>cmd.ExecuteNonQuery ( );</pre>

If you really want to use the dataset and the data adapter, <b>one </b>of the actions that are missing is the update of the adapter before closing the connection.

<pre>adapter.Update(ds,”SAdmission”);</pre>

Have a look at the following example on using datasets and data adapters:

<a href=”http://www.java2s.com/Code/VB/Database-ADO.net/Insertcommandwithparameters.htm”>Insert command with parameters</a>

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Shaz
    Thanks for helping me Update unable to find TableMapping['SAdmission'] or DataTable 'SAdmission'. But still iam getting above error. Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Windows.Forms Imports System.Data.SqlClient Public Class SAdmission Shared WithEvents con As SqlConnection Shared Sub Main() End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Me.Hide() Home.Show() End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim adapter As SqlDataAdapter Dim cmd As SqlCommand Dim ds As New DataSet Dim test As Boolean Dim conn As SqlConnection Dim sql As String conn = New SqlConnection("Data Source=NASSQLEXPRESS;Database=data2;Integrated Security=True; Connect Timeout=30;") sql = "Insert into SAdmission(RegistrationNo,Name,DateofBirth,Class,Nationality,MotherTongue,Religion,BloodGroup,SecondLanguage,Address1,Address2,PhoneNo,DateofJoining,FathersName,Occupation,MothersName,Occupation1)values(" 'RegistrationNo’,'Name’,'DateofBirth’,'Class’,'Nationality’,'MotherTongue’,'Religion’,'BloodGroup’,'SecondLanguage’,'Address1′,’Address2′,’PhoneNo’,'DateofJoining’,'FathersName’,'Occupation’,'MothersName’,'Occupation1′")" conn.Open() MsgBox("conection has open") test = conn.State 'to check the connection state MsgBox(test) cmd = New SqlCommand(sql, conn) MessageBox.Show(cmd.CommandText.ToString()) adapter = New SqlDataAdapter adapter.InsertCommand = cmd adapter.Update(ds, "SAdmission") conn.Close() MsgBox("You have successflly updated") End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Me.Close() End Sub End Class Plz check Shaz
    255 pointsBadges:
    report
  • carlosdl
    Did you take a look at the link I suggested for an insert example ?? As stated above, the Update method of the data adapter changes the data in the data source to match the data in the DataSet, but you don't have any data in the data set, and I think that is the problem. This is the description of the InsertCommand property: "A SqlCommand used during Update to insert records into the database that correspond to new rows in the DataSet." If you want to insert records into the database table through a dataset, you need to add those new records to the dataset first, and remember that the Update method will perform any necessary actions to synchronize information between the data source, and the dataset, so if your dataset has only 1 row, I guess that is what you will have in the underlying table after the update. Please, have a look at the following example on using datasets and data adapters: Insert command with parameters
    69,160 pointsBadges:
    report
  • Shaz
    Thanks for helping me but iam geeting errors plz have alook at my code and guide me.the following errors iam gettting. Before altering the dataset ========= After altering the dataset 1 | Nameera | 16/12/2006 Error: System.Data.SqlClient.SqlException: Incorrect syntax near '('. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at School.SAdmission.Button2_Click(Object sender, EventArgs e) in D:Visual Studio 2005SchoolSchoolSAdmission.vb:line 123 Connection Closed My code is as below Imports System Imports System.Collections Imports System.Data Imports System.IO Imports System.Xml.Serialization Imports System.Windows.Forms Imports System.Data.SqlClient Public Class SAdmission Shared Sub Main() End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Me.Hide() Home.Show() End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim thisConnection As New SqlConnection("Data Source=NASSQLEXPRESS;Database=data2;Integrated Security=True; Connect Timeout=30;") Dim sql As String = "SELECT * FROM SAdmission " Dim insertSql As String = "Insert into SAdmission(RegistrationNo,Name,DateofBirth,Class,Nationality,MotherTongue,Religion,BloodGroup,SecondLanguage,Address1,Address2,PhoneNo,DateofJoining,FathersName,Occupation,MothersName,Occupation1)values(" 'RegistrationNo’,'Name’,'DateofBirth’,'Class’,'Nationality’,'MotherTongue’,'Religion’,'BloodGroup’,'SecondLanguage’,'Address1′,’Address2′,’PhoneNo’,'DateofJoining’,'FathersName’,'Occupation’,'MothersName’,'Occupation1′")" Try Dim da As New SqlDataAdapter da.SelectCommand = New SqlCommand(sql, thisConnection) ' Create and fill Dataset Dim ds As New DataSet da.Fill(ds, "SAdmission") ' Get the Data Table Dim dt As DataTable = ds.Tables("SAdmission") Console.WriteLine("Before altering the dataset") For Each row As DataRow In dt.Rows Console.WriteLine("{0} | {1} | {2}", _ row("RegistrationNo").ToString().PadRight(10), _ row("Name").ToString().PadRight(10), _ row("DateofBirth").ToString().PadRight(10), _ row("Class").ToString().PadRight(10), _ row("Nationality").ToString().PadRight(10), _ row("MotherTongue").ToString().PadRight(10), _ row("BloodGroup").ToString().PadRight(10), _ row("SecondLanguage").ToString().PadRight(10), _ row("Address1").ToString().PadRight(10), _ row("Address2").ToString().PadRight(10), _ row("PhoneNo").ToString().PadRight(10), _ row("DateofJoining").ToString().PadRight(10), _ row("FathersName").ToString().PadRight(10), _ row("Occupation").ToString().PadRight(10), _ row("MothersName").ToString().PadRight(10), _ row("Occupation1").ToString().PadRight(10)) Next Dim newRow As DataRow = dt.NewRow() newRow("RegistrationNo") = "1" newRow("Name") = "Nameera" newRow("DateofBirth") = "16/12/2006" dt.Rows.Add(newRow) Console.WriteLine("=========") Console.WriteLine("After altering the dataset") For Each row As DataRow In dt.Rows Console.WriteLine("{0} | {1} | {2}", _ row("RegistrationNo").ToString().PadRight(10), _ row("Name").ToString().PadRight(10), _ row("DateofBirth").ToString().PadRight(10), _ row("Class").ToString().PadRight(10), _ row("Nationality").ToString().PadRight(10), _ row("MotherTongue").ToString().PadRight(10), _ row("BloodGroup").ToString().PadRight(10), _ row("SecondLanguage").ToString().PadRight(10), _ row("Address1").ToString().PadRight(10), _ row("Address2").ToString().PadRight(10), _ row("PhoneNo").ToString().PadRight(10), _ row("DateofJoining").ToString().PadRight(10), _ row("FathersName").ToString().PadRight(10), _ row("Occupation").ToString().PadRight(10), _ row("MothersName").ToString().PadRight(10), _ row("Occupation1").ToString().PadRight(10)) Next Dim insertCmd As New SqlCommand(insertSql, thisConnection) insertCmd.Parameters.Add("@RegistrationNo", _ SqlDbType.NVarChar, 10, "RegistrationNo") insertCmd.Parameters.Add("@Name", _ SqlDbType.NVarChar, 20, "Name") insertCmd.Parameters.Add("@DateofBirth", _ SqlDbType.NVarChar, 15, "DateofBirth") insertCmd.Parameters.Add("@Class", _ SqlDbType.NVarChar, 20, "Class") insertCmd.Parameters.Add("@Nationality", _ SqlDbType.NVarChar, 20, "Nationality") insertCmd.Parameters.Add("@MotherTongue", _ SqlDbType.NVarChar, 20, "MotherTongue") insertCmd.Parameters.Add("@Religion", _ SqlDbType.NVarChar, 20, "Religion") insertCmd.Parameters.Add("@BloodGroup", _ SqlDbType.NVarChar, 20, "BloodGroup") insertCmd.Parameters.Add("@SecondLanguage", _ SqlDbType.NVarChar, 20, "secondlanguage") insertCmd.Parameters.Add("@Address1", _ SqlDbType.NVarChar, 20, "Address1") insertCmd.Parameters.Add("@Address2", _ SqlDbType.NVarChar, 20, "Address2") insertCmd.Parameters.Add("@PhoneNo", _ SqlDbType.NVarChar, 20, "PhoneNo") insertCmd.Parameters.Add("@DateofJoining", _ SqlDbType.NVarChar, 20, "DateofJoining") insertCmd.Parameters.Add("@FathersName", _ SqlDbType.NVarChar, 20, "FathersName") insertCmd.Parameters.Add("@Occupation", _ SqlDbType.NVarChar, 20, "Occupation") insertCmd.Parameters.Add("@MothersName", _ SqlDbType.NVarChar, 20, "MothersName") insertCmd.Parameters.Add("@Occupation1", _ SqlDbType.NVarChar, 20, "Occupation1") da.InsertCommand = insertCmd da.Update(ds, "SAdmission") Catch ex As SqlException Console.WriteLine("Error: " & ex.ToString()) Finally thisConnection.Close() Console.WriteLine("Connection Closed") End Try End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Me.Close() End Sub End Class
    255 pointsBadges:
    report
  • carlosdl
    That is an error message from Sql Server. Your insert sql command seems to be incorrect. It should be something similar to this:
    "
    Dim insertSql As String = “Insert into SAdmission(RegistrationNo,Name,DateofBirth,Class,Nationality,MotherTongue,Religion,BloodGroup,SecondLanguage,Address1,Address2,PhoneNo,DateofJoining,FathersName,Occupation,MothersName,Occupation1) values (@RegistrationNo,@Name,@DateofBirth,@Class,@Nationality,@MotherTongue,@Religion,@BloodGroup,@SecondLanguage,@Address1,@Address2,@PhoneNo,@DateofJoining,@FathersName,@Occupation,@MothersName,@Occupation1)”
    "
    69,160 pointsBadges:
    report
  • Shaz
    Thank for helping it worked out,but the problem which values i have given in database r inserting in database.I dont want like that,I want in such a way any person will fill admission form the values should enter into database directly and store ,how to do it,i will not write values in coding.so give the solution plz shaz
    255 pointsBadges:
    report
  • Shaz
    In form in text box after entering valules,hitting with enter from keyboard is not working and i want after storing values in the database the form should beome clear for entering new values plz tell all above solutions Thanking u shaz
    255 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following