Problem Inserting Data Into A SQL Express Database
Feb 21, 2007
I am trying to implement a code behind page (in VB) to insert data into a sql express database but something is not right. Any help would be greatly appreciated. The following is the code I have:
Protected Sub SaveButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SaveButton.Click
Dim connString As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|News.mdf;Integrated Security=True;User Instance=True;Asynchronous Processing=true"
Dim sqlInsert As String = "INSERT INTO news_Article (Heading, Story, Author, Show_on_homepage) VALUES(@heading, @story, @author, @show)"
Dim conn As New System.Data.SqlClient.SqlConnection(connString)
Dim cmd As New System.Data.SqlClient.SqlCommand(sqlInsert, conn)
Dim heading = HeadingTextBox.Text
Dim story = StoryEditor.Value
Dim author = AuthorTextBox.Text
Dim show = ShowHPCheckBox.Checked
conn.Open()
cmd.BeginExecuteNonQuery()
conn.Close()
End Sub
Hello friends.... I am looking for 2 things(using c#.net or vb.net and sql svr 2000) 1.convert data from sql server 2000 database (say customers table from northwinds database) to a text file(separated by commas or just plain space) 2.Insert the data from text file back to database. Can someone pls give me the detailed code to achieve this....really need this on urgent basis.......Thank You.
Hi iam working with the form which has fields like AuditName,Industry Name,Company Name,Plant Name,Group Name,AuditStartedOn,Auditperiod upto,CreatedOn,createdby I have dropdownlists for Industry Name,Company Name,Plant Name,Group Name.Data will be filled into Industry Name,Group Name when pageloads from the database and later depending on industryname company name and depending on company name plant name ddl's wiil be filled. Later to insert this into the Audit table i had given the Stored procedure as : create procedure CreateAudit ( @AuditName nvarchar(50), @IndustryName nvarchar(50), @IndustryID int output, @CompanyName nvarchar(50), @CompanyID int output, @PlantName nvarchar(50), @PlantID int output, @GroupName nvarchar(50), @GroupID int output, @AuditStartedOn datetime, @AuditScheduledto datetime, @CreatedOn datetime, @CreatedBy int ) as begin //Here iam getting the Id of the industryname selected in the ddl from industry table into an output parameter @IndustryID select @IndustryID=Ind_Id_PK from Industry where Industry_Name=@IndustryName //Here iam getting the Id of the companyname selected in the ddl from company table into an output parameter @CompanyID select @CompanyID=Cmp_ID_PK from Company where Company_Name=@CompanyName //Here iam getting the Id of the plantname selected in the ddl from plant table into an output parameter @PlantID select @PlantID=Pl_ID_PK from Plant where Plant_Name=@PlantName //Here iam getting the Id of the Groupname selected in the ddl from Group table into an output parameter @GroupID select @GroupID=G_ID_PK from Groups where Groups_Name=@GroupName Insert into Audits(Audit_Name,Audit_Industry,Audit_Company,Audit_Plant,Audit_Group,Audit_Started_On,Audit_Scheduledto,Audit_Created_On,Audit_Created_By)values(@AuditName,@IndustryID,@CompanyID,@PlantID,@GroupID,@AuditStartedOn,@AuditScheduledto,@CreatedOn,@CreatedBy) end Later called these parameters into class file:
namespace xyz{ public class clsCreateAudit {SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["constr"]); SqlCommand cmd = new SqlCommand();SqlDataAdapter da = new SqlDataAdapter();public clsCreateAudit() { con.Open(); }public void CreateAudit(string Audit_Name, int Audit_Industry, int Audit_Company, int Audit_Plant, int Audit_Group, DateTime Audit_Started_On, DateTime Audit_Scheduledto, DateTime Audit_Created_On, string Audit_Created_By) { cmd.Connection = con;cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "CreateAudit"; SqlParameter AuditName = new SqlParameter();AuditName.ParameterName = "@AuditName";AuditName.DbType = DbType.String; AuditName.Value = Audit_Name;AuditName.Direction = ParameterDirection.Input; cmd.Parameters.Add(AuditName); SqlParameter AuditIndustry = new SqlParameter();AuditIndustry.ParameterName = "@IndustryName";AuditIndustry.Direction = ParameterDirection.Input; AuditIndustry.Value = Audit_Industry;AuditIndustry.DbType = DbType.String; cmd.Parameters.Add(AuditIndustry); SqlParameter IndustryID = new SqlParameter();IndustryID.ParameterName = "@IndustryID"; IndustryID.Direction = ParameterDirection.Output;IndustryID.DbType = DbType.Int32; //IndustryID.Size = 100; cmd.Parameters.Add(IndustryID);SqlParameter AuditCompany = new SqlParameter(); AuditCompany.ParameterName = "@CompanyName";AuditCompany.Direction = ParameterDirection.Input; AuditCompany.Value = Audit_Company;AuditCompany.DbType = DbType.String; cmd.Parameters.Add(AuditCompany);SqlParameter CompanyID = new SqlParameter(); CompanyID.ParameterName = "@CompanyID";CompanyID.Direction = ParameterDirection.Output; CompanyID.DbType = DbType.Int32; //IndustryID.Size = 100; cmd.Parameters.Add(CompanyID); SqlParameter AuditPlant = new SqlParameter(); AuditPlant.ParameterName = "@PlantName";AuditPlant.Direction = ParameterDirection.Input; AuditPlant.Value = Audit_Plant;AuditPlant.DbType = DbType.String; cmd.Parameters.Add(AuditPlant);SqlParameter PlantID = new SqlParameter(); PlantID.ParameterName = "@PlantID";PlantID.Direction = ParameterDirection.Output; PlantID.DbType = DbType.Int32; //IndustryID.Size = 100; cmd.Parameters.Add(PlantID);SqlParameter AuditGroup = new SqlParameter(); AuditGroup.ParameterName = "@GroupName";AuditGroup.Direction = ParameterDirection.Input; AuditGroup.Value = Audit_Group;AuditGroup.DbType = DbType.String; cmd.Parameters.Add(AuditGroup);SqlParameter GroupID = new SqlParameter(); GroupID.ParameterName = "@GroupID";GroupID.Direction = ParameterDirection.Output; GroupID.DbType = DbType.Int32; //IndustryID.Size = 100; cmd.Parameters.Add(GroupID);SqlParameter AuditStartedOn = new SqlParameter(); AuditStartedOn.ParameterName = "@AuditStartedOn";AuditStartedOn.Direction = ParameterDirection.Input; AuditStartedOn.Value = Audit_Started_On;AuditStartedOn.DbType = DbType.String; cmd.Parameters.Add(AuditStartedOn);SqlParameter AuditScheduledto = new SqlParameter(); AuditScheduledto.ParameterName = "@AuditScheduledto";AuditScheduledto.Direction = ParameterDirection.Input; AuditScheduledto.Value = Audit_Scheduledto;AuditScheduledto.DbType = DbType.String; cmd.Parameters.Add(AuditScheduledto);SqlParameter CreatedOn = new SqlParameter(); CreatedOn.ParameterName = "@CreatedOn";CreatedOn.Direction = ParameterDirection.Input; CreatedOn.Value = Audit_Created_On;CreatedOn.DbType = DbType.String; cmd.Parameters.Add(CreatedOn);SqlParameter CreatedBy = new SqlParameter(); CreatedBy.ParameterName = "@CreatedBy";CreatedBy.Direction = ParameterDirection.Input; CreatedBy.Value = Audit_Created_By;CreatedBy.DbType = DbType.Int32; cmd.Parameters.Add(CreatedBy); cmd.ExecuteNonQuery(); con.Close(); } } } Then i called these function into .aspx.cs file:
using xyz;protected void btn_CreateAudit_Click(object sender, EventArgs e) {SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["constr"]); PCRA.clsCreateAudit obj = new PCRA.clsCreateAudit(); SqlCommand cmd = new SqlCommand(); //Iam getting the Session(UID) from my login page.obj.CreateAudit(txt_AuditName.Text, Convert.ToInt32(ddl_Industry.SelectedItem.Value), Convert.ToInt32(ddl_Company.SelectedItem.Value), Convert.ToInt32(ddl_Plant.SelectedItem.Value), Convert.ToInt32(ddl_Group.SelectedItem.Value), Convert.ToDateTime(txt_StartingOn.Text.ToString()), Convert.ToDateTime(txt_AuditPeriod.Text.ToString()), System.DateTime.Now, Session["UID"].ToString());lbl_Mesg.Text = "Your Audit Details are added succesfully"; }
But iam getting an error here near obj.CreateAudit as: Input string was not in a correct format. I even want to know if my storedprocedure reaches the requirement which i specified. please help me with this.Its very urgent.
Following are statement which I am using when inserting data into database
INSERT INTO EMPLOYEE (NAME, TELEPHONE, PINNUM,CELLNUM) SELECT NAME,TELEPHONE,PINNUM FROM EMPLOYEE WHERE EMPLOYEEID=1
Noticed that I dont have CELLNUM In my select statment, but I want to insert that number from textbox which I have on my webform. can some one tell me how do i do this query so i can insert data into database.
my requirment is insert TableName and JourneyDate and FlightNumber alongwith otherdata at RunTime but i get error, I tried it several times. Table Structure is: Create Table HA142 ( JourneyDate DateTime primary key, FlightNo char(5)not null FOREIGN kEY REFERENCES FLIGHTS(FlightNo), FirstClassSeatAvalable int, BusinessClassSeatAvalable int, EconomyClassSeatAvalable int, FsWaitingAvalable int, BsWaitingAvalable int, EcWaitingAvalable int ) string flightno = drpFlightNo.SelectedItem.Text; string JourneyDate = Session["JourneyDate"].ToString(); string newStrign = ",18,42,280,3,7,35)"; SqlConnection myConn = new SqlConnection("workstation id=JASIM;packet size=4096;user id=ASPNET;data source=JASIM;persist security info=False;initial catalog=Test"); SqlCommand populateFlightTable = new SqlCommand("INSERT INTO "+flightno+" VALUES("+JourneyDate+","+flightno+newStrign,myConn); myConn.Open(); populateFlightTable.ExecuteNonQuery(); myConn.Close(); whenever compiler reached to populateFlightTable.ExecuteNonQuery(); I received error. i tried it to rectify several times but no result.plz hemp me...
Hi all, I want to insert some data that from a table into a database, and I got the error message-- "The variable name '@IssueID' has already been declared. Variable names must be unique within a query batch or stored procedure." Is it anything wrong with my code? Thanks a lot.
Dim row As Data.DataRow For Each row In table.Rows DataSource.InsertParameters.Add("IssueID", row(0)) DataSource.InsertParameters.Add("UserName", Session("loginName")) DataSource.InsertParameters.Add("IssueCost", row(1)) DataSource.InsertParameters.Add("IssueDesc", row(2)) DataSource.Insert() Next row
How can i insert test Data in to the Database,I want to insert one million records in to the table,This is to test Database Performance. Can anyone help me in this regard,Do we have any scripts for this purpose??? thanks Mar
Hello, is it possible to insert new data into a datbasealphabetically? For example when a user enters a new row of data, Iwant the row inserted in the correct order. I do not think this ispossible.Thank you for the help!
Hi everyone!Hope that someone can help me solving this problem.I have a form where the user can register by putting his private data. Each time that he submits his data, if he is using Internet Explorer, it will insert blank data into sql server database. But if user is using Firefox, everything is working well, and all data is inserted.What seems to be the problem?Why in IE, data is inserted as blank?!Thanks for your possible help and attention to this issue.Hope that someone can help me.Best regards,Mesk
hi I want to read data from XML file and insert that data from XML file into the Database Table From ASP.NET page.plz give me the code to do this using DataAdapter.Update(ds)
what i understand if if the data field is integer or money, not string, then i need to do a convert(datatype, value) in the insert but how come its still not working INSERT INTO [Product] ([Title], [Description], [Processor], [Motherboard], [Chipset], [RAM], [HDD], [OpticalDrive], [Graphics], [Sound], [Speakers], [LCD], [Keyboard], [Mouse], [Chassis], [PSU], [Price]) VALUES (@Title, @Description, @Processor, @Motherboard, @Chipset, @RAM, @HDD, @OpticalDrive, @Graphics, @Sound, @Speakers, @LCD, @Keyboard, @Mouse, @Chassis, @PSU, convert(smallmoney, @Price))
i am creating a database driven website and i am using a sql database. I have a database called company with fields in it to do with a company, I have created a company.cs file which sets the variables, properties and methods and so on. These work fine. I have also stored procedures and they are right as far as i know. i also have coding behind the buttons of my page when i try and update or insert to the database. I am having the problem of when i enter data into the text boxes and click update, nothing gets inserted or updated and whats worst of all no error message appears. The table is used for storing profile data about a user, when a user logs on they enter their profile data, if i manually enter this data and input the username of a user into the username field within the database then the data appears fine in the textboxes of the update info page but the data will not insert or update. i have checked all the little things and i am stressing out cos i am running out of time and cannot find the problem...........please could someone help me!!!!! thanks
Heres my coding to some of my pages to help you.... the code behind the button using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using FYPtestsite.Classes; public partial class Employer_employerprofile : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) {if (!Roles.IsUserInRole(ConfigurationManager.AppSettings["employerrole"])) {Response.Redirect("~/Error1.aspx"); }Company objCompany = Company.GetCompany(Profile.UserName);if (objCompany != null) {
Hi! I have a vb.net program that writes data to an SQL Server database using ADODB. The problem is if I put the code that writes data to the database in between BeginTrans() and CommitTrans(), the database is not updated. Here is the flow of my program:
Dim connection as ADODB.Connection 'setup the connection to the SQL Server database here
connection.BeginTrans()
InsertData() ' --> data is written to the database ' this function works properly
connection.CommitTrans()
If I comment out the BeginTrans() and CommitTrans() functions, the data is properly inserted into the database.
Does an SQL Server database requires special settings to support transactions?
I have question on lock on table in SQL Server while inserting data using multiple processes at a single time into same table.Here are my questions on this,
1) Is it default behavior of SQL server to lock table while doing insert? 2) if yes to Q1, then how we can implicitly mention while inserting data. 3) If I have 4 tables and one table is having foreign keys from rest of the 3 tables, on this scenario do I need to use the table lock explicitly or without that I can insert records into those tables?
I developed a console application that will continually check a messagequeue to watch for any incoming data that needs to be inserted into MSSQL database.What would be a low-cost method I could use inside this consoleapplication to make sure the MS SQL database is operational before Iperform the insert?
I have two databases im working with, one is our public database GRM_Public, and the other is our production database GRM_Prod.
I'm trying to import data from a field called 'oldscreencodes1' from GRM_Prod.Transfer table into a field called 'Sale1Type' in GRM_Public.Real_land table.
For some reason I can't get this to work, I have in the past imported datatables from our production DB to our public DB by using 'insert into' but I've never inserted data into a single field within a datatable and I think I'm over thinking this process.
Is a 'join' necessary in order to accomplish this?
4 Layered Web Application for Inserting data into a database using sql server as the back end and a web form as the front end using C# . Can someone provide with code as I am new to this architecture and framework. Better send email. Thanks In Advance, A New Bie
I'm trying to learn some VB programming with the VB 2005 Express Absolute Beginner Series video tutorials (which I think is great) and have come across a problem that I can't solve.
When I follow the instructions in Lesson 9 (Databinding Data to User Interface Controls) my application will display the data from the database correctly and I can edit it (and as long as the debugger is running the data remains changed). However, the changes won't propagate back to the database. I don't get any error messages but after I edit the data, save (with the save button on the BindingNavigator toolbar), and end debugging the data in my database remains unchanged. When I use a MessageBox to show how many rows where edited/updated in the
I get the correct number back. I'm sure the problem is not due to coding errors since I've also tried running the accompanying Lesson 9 project file that can be downloaded from MSDN and the problem persists.
I'm using Windows XP SP2, SQL Server 2005 Express Edition and VB 2005 Express Edition. I've tried installing SQL Server 2005 Express with a number of different settings, including default settings, but it doesn't make any difference.
Would greatly appreciate any feedback on this as I'm keen to resolve this problem so I can get on with the next tutorial lesson.
I have two SQL Express database and I want to do two things. One is to transfer a table over to the other database. Two, move the files from one table in one database to another. Please let me know when you get a chance.
Each time I press submit to insert data into the database I receive the following message. I use the same code on another page and it works fine. Here is the error:
Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 125: MyCommand.Parameters("@Balance").Value = txtBalance.Text Line 126: Line 127: MyCommand.Connection.Open() Line 128: Line 129: Try
[NullReferenceException: Object reference not set to an instance of an object.] CreditRepair.CreditRepair.Vb.Creditor_Default.btnSaveAdd_Click(Object sender, EventArgs e) in c:inetpubwwwrootCreditRepairCreditor_Default.aspx.vb:127 System.Web.UI.WebControls.Button.OnClick(EventArgs e) System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain()
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If (Page.IsValid) Then
Dim DS As DataSet Dim MyCommand As SqlCommand
Dim AddAccount As String = "insert into AccountDetails (Account_ID, Report_ID, Balance) values (@Account_ID, @Report_ID, @Balance)"
MyCommand = New SqlCommand(AddAccount, MyConnection)
Using visual web Developer 2008 express edition. I am trying to insert 2 text boxes with a label of FirstName and LastName into a SQL express database. I am using a submit button to accomplish this. I have a good connection to the database called FormData. When the textbox is filled in with firstname and lastname and the submit button is pressed, I want to be able to go to the database and do a select * from tablename and see the updated table.... Here is my code so far: any help would be greatly appreciated....<%@Import Namespace="System.Data.SqlClient" %> <%@Import Namespace="System.Data" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> </script> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Untitled Page</title> <style type="text/css"> #form1 {height: 292px;width: 431px; } </style></head> <body> <form id="form1" runat="server"><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FormDataConnectionString %>" SelectCommand="SELECT * FROM [Table_1]"></asp:SqlDataSource> <p style="width: 78px; height: 19px; font-size: small; font-family: Verdana; margin-right: 0px; top: 76px; left: 13px; position: absolute;"> FirstName</p> <p style="width: 80px; height: 21px; font-family: Verdana; font-size: small; top: 138px; left: 10px; position: absolute;"> LastName</p>
I have couple question about inserting values into tables. First of all I like to know , how do you start on designing multiple table data entry screens? Do you design bound items? or you would design the form without bounding any component and insert data with ado (manually)?
Also, I want to know which way is better to insert tables? ADO or bound items and data forms. AS you all know after making components (objects like textboxes) you could add a new row and update the table like this.
me.mydataAdapter.update(me.mydataset.mytable)
how ever I know there is more way to do that which shown below;
Dim newRow As KasaDataSet.transactionsRow = Me.KasaDataSet1.transactions.NewRow()
newRow.adet = Me.txtquantity.Text
newRow.trproductid = CInt(Me.txtproductid.Text)
Me.KasaDataSet1.transactions.Rows.Add(newRow)
on 3rd line TRPRODUCTID is uniqueIdentifier type of sqlexpress (FK) field. I get error message when I try to rund this message saying that;
Error 1 Value of type 'Integer' cannot be converted to 'System.Guid'
Do you have a solution for me?
Or should I use ADO.net old ASP like connection and user SQL insert command??
Hello there,I'm to asp.net, so please be patient :DMy question is, how do I simply add some data to my database? - With vb.net code, not a grid view or something like that..I want to connect to my database, insert some data to a table.It shouldn't be that hard?- Hope someone will take the 5 minutes, and help me :)Regards Jeppe
How to move some tables with data & procedures etc from 1 database to another in sql server 2005 express edition. i did by scripting but i transfer tables and procedures and not data data is the problem. tnx
Below and far below are my SQL command and raw data. In the data, each lot has 13 records, for example, T6L09641 has 13 records. What I am working on is to average the Total, Large, Middle and Small for each lot as a representative instead of listing every record. So, I wrote a SQL express and put it in my Access and run it. However, I got an error message "ORA-00905:missing keyword(#905)". I double checked it again and found nothing stange. Someone can help me to modify it?
SELECT LINE_ID, LOT_ID, AVG(TSIZE_QTY_PA)/10000 Total, AVG(LSIZE_QTY_PA)/10000 Large, AVG(MSIZE_QTY_PA)/10000 Min, AVG(SSIZE_QTY_PA)/10000 Small, RCV_TIME Time FROM IWMES.DIMES_PPTU_PT_IPCRST Where (LINE_ID='L401') GROUP BY LOT_ID HAVING RCV_TIME BETWEEN 12/10/2006 00:00 AND 12/13/2006 00:00 ORDER BY LOT_ID
Raw Data http://photo.pchome.com.tw/vitaminb6/116597990115
I have a webform that has 2 calendars that i use to insert a dateFrom and dateTo into a sql database table that has the type smalldatetime. When i insert into the database i get yyyy-mm-dd hh:mm:ss but i just want the yyyy-mm-dd not the time. how can i do this with my asp.net c# code? I also has a datetime.Now() that does the same thing.1 string dateNow = DateTime.Now.ToShortDateString(); 2 3 string myConnectionString = @"Data Source=SRVWEB02SQLEXPRESS;Initial Catalog=strukton_se;User ID=user;Password=secret"; 4 5 SqlConnection myConnection = new SqlConnection(myConnectionString); 6 string myInsertQuery = "INSERT INTO jobs (name, description, contact, datePosted, dateFrom, dateTo) Values(@name, @description, @contact, @datePosted, @dateFrom, @dateTo)"; 7 SqlCommand myCommand = new SqlCommand(myInsertQuery); 8 myCommand.Parameters.AddWithValue("name", TextBoxName.Text); 9 myCommand.Parameters.AddWithValue("description", TextBoxDescription.Text.Replace(" ", "<br/>")); 10 myCommand.Parameters.AddWithValue("contact", TextBoxContact.Text.Replace(" ", "<br/>")); 11 myCommand.Parameters.AddWithValue("datePosted", dateNow); 12 myCommand.Parameters.AddWithValue("dateFrom", Calendar1.SelectedDate.ToShortDateString()); 13 myCommand.Parameters.AddWithValue("dateTo", Calendar2.SelectedDate.ToShortDateString()); 14 15 ButtonSubmit.Enabled = false; 16 17 myCommand.Connection = myConnection; 18 myConnection.Open(); 19 myCommand.ExecuteNonQuery(); 20 myCommand.Connection.Close(); Thanks.
I have to export data from SQL Server 2005 express to Access database. I have done many import/export using DTS package via SQL 2000. I don't have BI installed in my SQL SERVER 2005 Express. I understand that I have to use SSIS for sql server 2005. Any help is greatly appreciated.