|
Hello everyone. I have been trying for a while to save records to sql.
i have created a database called SchoolDB. i hv the feild listed below in the table StudentInfo.
unfortunately i haven't succeeded. I tried many ways.
Here is one of them.
Dim _cn As New SqlConnection
Dim _cnstring As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=SchoolDB;Integrated Security=True;"
Dim _cn As New SqlConnection(_cnstring)
Dim cmd As New SqlCommand
_cn.Open()
cmd.Connection = _cn
cmd.CommandText = "INSERT INTO StudentInfo([Code], [Full-Name], [Position], [Title], [Phone-Number], [Address], [Date-Time]) VALUES('" & Form1.txtCode.Text & "', '" & Form1.txtName.Text & "', '" & Form1.cmbPosition.Text & "', '" & Form1.cmbTitle.Text & "','" & Form1.txtPhoneNumber.Text & "',' " & Form1.txtAddress.Text & "', '" & Form1.DateTimePicker1.Value & "');"
cmd.ExecuteNonQuery()
_cn.Close()
Can you help me please :/
Any help would be appreciated
|
|
|
|
|
What kind of error did you get? Did you try debugging?
Copy the query and run it directly on your database. Check if it inserts data into the table.
|
|
|
|
|
I am getting :
Quote: Violation of PRIMARY KEY constraint 'PK_StudentInfo'. Cannot insert duplicate key in object 'dbo.StudentInfo'.
but i am sure 100% that there is no record that have the same code.
So i don't know what the problem is :/
|
|
|
|
|
Quote: Private Sub StudentInfoBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles StudentInfoBindingNavigatorSaveItem.Click
Me.Validate()
Me.StudentInfoBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.SchoolDBDataSet)
End Sub
i have tried this. but it won't save to the database. it will only out a new record in the current form
|
|
|
|
|
Quote: Quote: Private Sub StudentInfoBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles StudentInfoBindingNavigatorSaveItem.Click
Me.Validate()
Me.StudentInfoBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.SchoolDBDataSet)
End Sub
i have tried this. but it won't save to the database. it will only out a new record in the current form
|
|
|
|
|
Start by reading about SQL Injection[^], and then fixing the vulnerability in your code.
cmd.CommandText = "INSERT INTO StudentInfo([Code], [Full-Name], [Position], [Title], [Phone-Number], [Address], [Date-Time]) VALUES (@Code, @Name, @Position, @Title, @PhoneNumber, @Address, @DateTime);"
cmd.Parameters.AddWithValue("@Code", Form1.txtCode.Text)
cmd.Parameters.AddWithValue("@Name", Form1.txtName.Text)
cmd.Parameters.AddWithValue("@Position", Form1.cmbPosition.Text)
cmd.Parameters.AddWithValue("@Title", Form1.cmbTitle.Text)
cmd.Parameters.AddWithValue("@PhoneNumber", Form1.txtPhoneNumber.Text)
cmd.Parameters.AddWithValue("@Address", Form1.TxtAddress.Text)
cmd.Parameters.AddWithValue("@DateTime", Form1.DateTimePicker1.Value)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
..and after that?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'd suggest posting the DDL of the StudentInfo table.
If the Code column is the primary key, then the statement "i am sure 100% that there is no record that have the same code[^]" doesn't match what the database knows. If that's the case, then my money's on the database being correct!
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: I'd suggest posting the DDL of the StudentInfo table.
For a moment, I'd thought you'd bring up the missing using-directive.
Richard Deeming wrote: If the Code column is the primary key, then the statement "i am sure 100% that there is no record that have the same code[^]" doesn't match what the database knows. If that's the case, then my money's on the database being correct!
Good bet, and more helpfull than a non-related best practice.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: Good bet, and more helpfull than a non-related best practice.
"Best practice" doesn't quite cover it. SQL Injection remains the biggest security vulnerability[^] in the wild today. If I see someone posting code which is susceptible to SQL Injection, I'll point that out in the hopes that they will appreciate and learn from the advice.
If someone asked you, "Every time I pour coffee into my computer it smells funny - what's the best brand of air-freshener to use?", would you advise them on the best brand of air-freshener, or would you point out the dangers of pouring coffee into their computer?! 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Quote: Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=SchoolDB;Integrated Security=True;"
Dim _cn As SqlConnection = New SqlConnection(strconnection)
_cn.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "INSERT INTO StudentInfo([Code], [Full-Name], [Position], [Title], [Phone-Number], [Address], [Date-Time]) VALUES (@Code, @Name, @Position, @Title, @PhoneNumber, @Address, @DateTime);"
cmd.Parameters.AddWithValue("@Code", Form1.txtCode.Text)
cmd.Parameters.AddWithValue("@Name", Form1.txtName.Text)
cmd.Parameters.AddWithValue("@Position", Form1.cmbPosition.Text)
cmd.Parameters.AddWithValue("@Title", Form1.cmbTitle.Text)
cmd.Parameters.AddWithValue("@PhoneNumber", Form1.txtPhoneNumber.Text)
cmd.Parameters.AddWithValue("@Address", Form1.txtAddress.Text)
cmd.Parameters.AddWithValue("@DateTime", Form1.DateTimePicker1.Value)
Dim cmd1 As New SqlCommand
cmd1.CommandText = "select * from StudentInfo"
MsgBox(cmd1.CommandText.ToString)
_cn.Close()
End Sub
Hello again. The code won't give me any errors. But how can i view the newly inserted record in my database (SQl) ??
Thank you 
|
|
|
|
|
You're missing a call to cmd.ExecuteNonQuery(), which would actually insert the record. This needs to come after the last cmd.Parameters.AddWithValue(...) line.
...
cmd.Parameters.AddWithValue("@DateTime", Form1.DateTimePicker1.Value)
cmd.ExecuteNonQuery()
Assuming the Code column is your primary key, you need to specify the filter in the WHERE clause of your SELECT query:
cmd1.CommandText = "select * from StudentInfo where Code = @Code"
cmd1.Parameters.AddWithValue("@Code", Form1.txtCode.Text)
If the Code column is not your primary key, then you'll need to post the structure of your StudentInfo table so we can see what's going on.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Message Closed
modified 13-Nov-13 9:00am.
|
|
|
|
|
Your save routine is still missing the call to cmd.ExecuteNonQuery(), which should be just before the _cn.Close() line. Without it, the record won't be saved.
Your remove routine always deletes the record where code = 1; since the command text doesn't reference the @code parameter, the value you've set is never used. It also deletes the record before asking the user whether they want to delete the record, making the question meaningless.
You might also want to look at wrapping the connection and command objects in a Using block[^] to make sure they get cleaned up properly even if there's an exception:
Using con As New SqlConnection(strconnection)
Using cmd As SqlCommand = con.CreateCommand()
...
End Using
End Using
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
SAVE BUTTON
-------------
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Try
Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=SchoolDB;Integrated Security=True;"
Dim _cn As SqlConnection = New SqlConnection(strconnection)
Dim cmd As New SqlCommand
_cn.Open()
cmd.CommandText = "INSERT INTO StudentInfo([Code], [Full-Name], [Position], [Title], [Phone-Number], [Address], [Date-Time]) VALUES (<a href="/Members/code">@Code</a>, <a href="/Members/Name">@Name</a>, <a href="/Members/position">@Position</a>, <a href="/Members/title">@Title</a>, @PhoneNumber, <a href="/Members/address">@Address</a>, <a href="/Members/datetime">@DateTime</a>);"
cmd.Connection = _cn
cmd.Parameters.AddWithValue("<a href="/Members/code">@Code</a>", Val(Me.t1.Text))
cmd.Parameters.AddWithValue("<a href="/Members/Name">@Name</a>", Me.t2.Text)
cmd.Parameters.AddWithValue("<a href="/Members/position">@Position</a>", Me.c1.Text)
cmd.Parameters.AddWithValue("<a href="/Members/title">@Title</a>", Me.c2.Text)
cmd.Parameters.AddWithValue("@PhoneNumber", Me.t5.Text)
cmd.Parameters.AddWithValue("<a href="/Members/address">@Address</a>", Me.t6.Text)
cmd.Parameters.AddWithValue("<a href="/Members/datetime">@DateTime</a>", Me.t7.Value)
cmd.ExecuteNonQuery()
_cn.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
REMOVE BUTTON
-------------
Private Sub btnRemove_Click(sender As Object, e As EventArgs) Handles btnRemove.Click
Me.StudentInfoBindingSource.RemoveCurrent()
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=SchoolDB;Integrated Security=True;"
Dim _cn As SqlConnection = New SqlConnection(strconnection)
_cn.Open()
cmd.Connection = _cn
cmd.CommandText = "Delete From studentInfo where code= " & Val(t1.Text)
If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
MsgBox("Operation Cancelled")
Exit Sub
Else
cmd.ExecuteNonQuery()
End If
Catch ex As Exception
MessageBox.Show("Error while deleting record on table..." & ex.Message, "Delete Records")
Finally
con.Close()
End Try
End Sub
|
|
|
|
|
Almost perfect!
For completeness, I'd be inclined to use a parameter in the remove routine as well:
cmd.CommandText = "delete from StudentInfo where Code = @Code"
cmd.Parameters.AddWithValue("@Code", t1.Text)
The Val function[^] converts the text to a number, so you're not going to get SQL Injection from that. However, if you get in the habit of always using parameters, you're less likely to accidentally introduce SQLi in the future.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Okay. Thank You
|
|
|
|
|
Member 10388494 wrote: Here is one of them.
That "many ways don't work" means little; I'd be more interested in what you mean if you say that *this* version doesn't work -
Does it throw an exception? Does it insert the record or not?
Member 10388494 wrote: Violation of PRIMARY KEY constraint 'PK_StudentInfo'. Cannot insert duplicate key in object 'dbo.StudentInfo'.
Sounds like you're inserting the same record twice.
Member 10388494 wrote: <layer>but i am sure 100% that there is no record that have the same code.
What fields are named in "PK_StudentInfo"? How are you sure that the record isn't in there? Can you delete everything in there and see if it saves the record once?
How does your update-statement look?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
The reason that it isn't working is because you have created a primary key in your table, but because you haven't actually specified a value for it, you are attempting to insert 0 into it (assuming it's of type int). What you need to do is open up the table definition and convert this field into an identity column, and let the database take care of this for you. Alternatively, you can be really cool and convert that column to a type that supports Guids, and assign your own unique value in code.
|
|
|
|
|
Hello. how can i store a radiobutton in sql?
i have RDFemale fro a female and RDMale fro a male.
i tried this:
_cn.Open()
Dim sqlstring As String = "Insert into Student values('" & txtCode.Text & "', '" & txtName.Text & "', '" & RDFemale.value & "', '" & cmbPosition.Text & "','" & cmbTitle.Text & "', '" & txtPhoneNumber.Text & "', '" & txtAddress.Text & "','" & DateTimePicker1.Value & "',' " & cmbPmode.Text & "', '" & txtRemarks.Text & "');"
Dim _cmd As New SqlCommand(sqlstring, _cn)
_cmd.ExecuteNonQuery()
_cn.Close()
MsgBox("Record saved!!", MsgBoxStyle.Information, "Successfully saved")
End Sub
|
|
|
|
|
You can't store it directly. Since you are referring to a RDMale and a RDFemale I'd recommend you to store it as an enum.
This enum may look like that:
enum Gender{
Male,
Female
};
Afterwards you are able to store the value into a database like a normal enum, you only have to parse it to a numeric value before you store it.
|
|
|
|
|
You would store the result of the radio button as either an int or a bit (assuming that you are using SQL Server).
i.e.
dim Male as boolean = RDMale.checked
if you test that line in your application you will see that it will return true if you have checked that button or false if you haven't
Also I would have a look at using SQLParameters as this will save you from SQL Injection have a look at this StackOverflow question the actual question will show you how to set it up in VB.NET
http://stackoverflow.com/questions/16765837/set-sqlparameter-in-vb-net[^]
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Let me Explain about the entire structure of what we are doing before start ask the question
Our client required Custom Primary keys for all our transactions. To create the key, we are using store procedure which require an input parameter named "SiteCode". This store procedure will create sequence keys for every transaction in the application we have made
Every transaction insert we have written a Stored procedure and inside this sp's we have call the key generating SP before we do insert statements and we have given those key as a primary key to the insert statement. So the actual "SiteCode" input parameter has passed to every transaction SP's as an input parameter
We have created DB first entity Model and all the entity class has been created
Now We have a screen in WPF which capture the data for the models Customer, Address, Contact information.
so Instead of getting model one by one and do save changes. we would like to map the stored procedure function (used to insert the values) with respective model
for example CutomerRef attribute to sp_CreateCustomer SP in entity framework
While we are doing this mapping we can mapp all the fields except the "SiteCode" field which don't have reference in any entity model.
We have tried to create partial class to the cutomerRef model and add this property in to that partial class and try to map with SP it doesn't work Also, tried to create the field in the model itself in entity model and map with the sp parameters also not working.
We know, we can do this to call sps from stored procedure but we need them to do just by Context.Customer.Add(customerModel)
Any help would be appriciated
Regards,
Ansari
|
|
|
|
|
Hi,
I know that this question also relates to Excel but I wanted to know if there's a way or if somebody can direct me how can I access a PowerPivot table in Excel and manipulate it i.e. update data, save data, retrieve data etc'.
Basically my purpose is to interact with the PowerPivot table so I can display the data and update it from a web browser.
Avi
|
|
|
|
|
Try and give this[^] a go.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
