Data Insert Via SQL Express Using VB Issue

Jun 27, 2006

I've been doing a lot of research lately to determine the most secure way to issue an insert statement to insert my data into my SQL database.  I'm coding this following using Visual Basic:

 Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim strConn As String = "server=.SQLEXPRESS;database=C:SJ-siteSJApp_DataSJDb.mdf; User Id=user; password=password"
Dim MyQuery As String = "Insert into sjTable (PostID, Category, TitleofPost, Description, Price, Loc, ContactInfo, DatePosted, Disclaimer) Values (@Category, @TitleofPost, @Description, @Price, @Loc, @ContactInfo, @DatePosted)"
Dim MyConn As New Data.SqlClient.SqlConnection(strConn)
Dim cmd As New Data.SqlClient.SqlCommand(MyQuery, MyConn)
Dim curDate As String
On Error Resume Next

MyConn.Open()
If Err.Number <> 0 Then
MsgBox("There was an error connecting to the database.", MsgBoxStyle.OkOnly)
Err.Number = 0
End If

curDate = Now()
With cmd.Parameters
.Add(New System.Data.SqlClient.SqlParameter("@Category", categoryDropDown.Text))
.Add(New System.Data.SqlClient.SqlParameter("@TiteofPost", titleTextbox.Text))
.Add(New System.Data.SqlClient.SqlParameter("@Description", descriptionTextbox.Text))
.Add(New System.Data.SqlClient.SqlParameter("@Price", priceTextbox.Text))
.Add(New System.Data.SqlClient.SqlParameter("@Loc", locationTextbox.Text))
.Add(New System.Data.SqlClient.SqlParameter("@ContactInfo", contactTextbox.Text))
.Add(New System.Data.SqlClient.SqlParameter("@DatePosted", curDate))
End With
If Err.Number <> 0 Then
MsgBox("There was an error inserting the data into the database.", MsgBoxStyle.OkOnly)
Err.Number = 0
End If

MyConn.Close()
If Err.Number <> 0 Then
MsgBox("There was an erro closing the database connection.", MsgBoxStyle.OkOnly)
Err.Number = 0
End If
Server.Transfer("submitted.aspx")


End Sub The code gives no errors, it directs me straight to the
submitted.aspx page but when I query the database it is not inserting
any data into my database.  .

My Error log shows the following:
2006-06-27 12:14:16.68 spid51      Starting up database 'C:SJ-SITESJAPP_DATASJDB.MDF'.
2006-06-27 12:14:26.93 Logon       Error: 17828, Severity: 20, State: 3.
2006-06-27 12:14:26.93 Logon       The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: <local machine>]

I am able to log into the database using my account via SQL Server Management Studio Express.  I've been all over the forums and google looking for a solution but everything I've been trying to mock up from other people's code hasn't helped.  Thanks in advance for your assistance

View 3 Replies


ADVERTISEMENT

Insert Data Into SQL SERVER EXPRESS Edition

Apr 20, 2007

I'm completely new to ASP.NET. I want to insert data to the SQL SERVER EXPRESS database.When the button clicks i want to add the details into database.This is the coding i did with online help.
Can you help me to do a good coding ad some error showing on my 7th line("connection.Open()")..
And can you advise me to do correct coding for how is get data from database and display it on web page.
Thanks in advance.And here is the coding i did to Insert data,
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connection As New SqlConnection(" database=share")
Dim cmd As New SqlCommand("INSERT INTO sdetails (cid, cname, date, qty, pps)VALUES('" & txtcid.Text & "','" & txtcname.Text & "','" & txtdate.Text & "','" & txtqty.Text & "','" & txtpps.Text & "')", connection)
 
connection.Open()
connection.Close()
End Sub

View 2 Replies View Related

Insert Data To MsSQL Express Database?

Jan 4, 2008

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

View 16 Replies View Related

SSMS Express: Create TABLE &&amp; INSERT Data Into Table - Error Msgs 102 &&amp; 156

May 18, 2006

Hi all,

I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise.

Thanks in advance,

Scott Chang

///////////--SQLQueryCroomLabData.sql--///////////////////////////
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
VALUES (2, 'MW2', 'Dichloroethene', 1.00)
VALUES (3, 'MW2', 'Trichloroethene', 20.00)
VALUES (4, 'MW2', 'Chloroform', 1.00)
VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
VALUES (6, 'MW6S', 'Acetone', 1.00)
VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
VALUES (9, 'MW6S', 'Chloroform', 1.00)
VALUES (10, 'MW6S', 'Methylene Chloride', 1.00
VALUES (11, 'MW7', 'Acetone', 1.00)
VALUES (12, 'MW7', 'Dichloroethene', 1.00)
VALUES (13, 'MW7', 'Trichloroethene', 1.00)
VALUES (14, 'MW7', 'Chloroform', 1.00)
VALUES (15, 'MW7', 'Methylene Chloride', 1.00
VALUES (16, 'TripBlank', 'Acetone', 1.00)
VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
VALUES (19, 'TripBlank', 'Chloroform', 0.76)
VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
//////////Parse///////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
////////////////Execute////////////////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.

View 7 Replies View Related

SQL Server 2008 :: Insert Data Into Table Variable But Need To Insert 1 Or 2 Rows Depending On Data

Feb 26, 2015

I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)

I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !

Below is the code i have at the moment

declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT

set @startdate = '2015-01-01'
set @enddate = '2015-01-31'

[Code] .....

View 1 Replies View Related

SQL Express Database Table Data Insert Into Another Database

Apr 15, 2007

Hi,

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.

Thanks,

Kyle

View 8 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

Apr 9, 2007

Hello
 I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

 and here goes the  code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
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 AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"


Could You please tell me what am I missing here ?
Thanks a lot.
 

View 5 Replies View Related

Can't Insert New Data To Sql Using Sqldatasource.insert, Web Forms And A Master Page

Sep 11, 2006

Hello, I'm new to the forum and new to SQL, ASP.NET, etc.  I am creating an intranet site for my company in VS 2005 and have run into a very annoying problem that I can't seem to solve.  I have tried Googling it and came up empty.  I have a database in SQL Express 2005 and my website will be accessing several tables within the database.  I can retrieve info just fine and I can update, delete, etc just fine using gridview or other prebuilt tools, but when I add a few text boxes and wire a button to the SqlDataSource.Insert() command, I get a new record that is full of null values except for the identity key I have set.  The kicker is that I am also using a master page and when I duplicate the web page without the master page link, everything works just fine.  The following snippets show what I'm doing:<InsertParameters><asp:FormParameter Name="Name" Type="String" FormField="txtName" /><asp:FormParameter Name="Location" Type="String" FormField="ddlLocation" /><asp:FormParameter Name="Issue" Type="String" FormField="txtProblem" /></InsertParameters>Of course I match the formfields to the text boxes, create an onclick event for my button, the sqldatasource is configured correctly, it just doesn't work with the master page no matter what I do. Any help would be appreciated. Thanks

View 3 Replies View Related

Insert Command Fails When I Want To Insert Records In Data Table

Apr 20, 2008

On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer  form that logged in users have access to.
it has 2 questions including a text box for Q1 and  dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
 
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
 Be very helpful if somebody could check the code and advise where the problem is??
 
 
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
 
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
 
</tr>
</table>
  Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
 
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
 
 
</asp:SqlDataSource>
</asp:WizardStep>
 
 Event Handler
 
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
 SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
 
}
 

View 1 Replies View Related

How To Insert Data From A File Into Table Having Two Columns-BULK INSERT

Oct 12, 2007



Hi,
i have a file which consists data as below,

3
123||
456||
789||

Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.


BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')

but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.

can anyone help me how to do this?

Thanks,
-Badri

View 5 Replies View Related

Data Connections For SQL Server Express In Visual Studio Express

Jan 27, 2006

I have an Excel add-in that connects to a SQL Server Express 2005database. I've decided to create a configuration piece for this add-inin Visual Studio 2005 Express. I added a data connection using the dataconnection wizard and all appeared to go well. Anyways when I attemptto open SQL Server Express to administer the database, it was corruptedand I had to restore it.I eventually got it to work correctly (I'm pretty sure I followedpretty much the same steps as before), but I was just wondering ifanyone had experienced problems like this? I find it a bit scary thatit may be that easy to corrupt the database by just creating a dataconnection.

View 2 Replies View Related

VWD Express 2005 Doesn't See My SQL Express For Data Connections

Mar 24, 2007

I have a laptop and a desktop that I both installed VWD and SQL Express.   from my laptop, i see my laptop's name in the Server Name under data connections, but on the desktop, I only see an older Win2K Server with SQL 2000 in the list. Anyone know why my desktop doesn't show it's NAME?I can create new tables, users and all that stuff from the Management area. Thanks 

View 3 Replies View Related

How To Insert Binary To SQL Express By VBA?

Oct 4, 2007

Hi all,

In the end of the post, there is my vba functions and query to create datatable. I got a run-time error message on the red line in the function. It says: [sql native client] string data right truncation. Did I do sometime wrong? Thanks for your time!

-L

Sub test()
Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")
Dim mConn As ADODB.Connection
'read the file data to bytes
'Specify stream type - we want To get binary data.
BinaryStream.Type = adTypeBinary
'Open the stream
BinaryStream.Open
'Load the file data from disk To stream object
BinaryStream.LoadFromFile file
Dim mCmd As ADODB.Command
Set mCmd = New ADODB.Command
With mCmd
' define query
.CommandText = "INSERT INTO TEST (FDATA) " & _
"VALUES (?)"
.CommandType = adCmdText
' add parameter
.Parameters.Append .CreateParameter("@P1", adVarBinary, _
adParamInput, BinaryStream.Size, BinaryStream.Read)
End With
' open db connection
mConn.ConnectionString = _
"Driver={SQL Native Client};Server=.SQLEXPRESS;Database=XXXX;Trusted_Connection=yes;"
mCmd.ActiveConnection = mConn
'run-time error: [sql native client] string data right truncation
mCmd.Execute
mConn.Close
End Sub



GO
/****** Object: Table [dbo].[TEST] Script Date: 10/04/2007 11:06:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FDATA] [varbinary](max) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

View 3 Replies View Related

SQL Express 2005 Insert Command

Aug 30, 2006

I have the following insert parameter and insert command which are attached to a templated textbox, I have made the textbox's visible property false so the user will not enter any text. I have a session variable session("test") = 500. I would like the value of the session variable to be inserted into the testcode field. I can not seem to find any workable syntax.<InsertParameters> <asp:Parameter Name="TestlCode" Type="Int32" />InsertCommand="INSERT INTO [Mycd] ( [TestlCode], ..   VALUES ( @testcode ,

View 1 Replies View Related

SQL Server Express Bulk Insert

Oct 25, 2006

does anyone know how to do a bulk insert in SQL Server Express thanks Marcel

View 2 Replies View Related

SQL Express 'hangs' When Executing An INSERT

Feb 14, 2008

Periodically an INSERT query takes anywhere from 30 to 90 sec's to execute. But once it completes, execution times then return to 0 or 1 sec. This has begun to occur more and more frequently, typically every thirty minutes or so now.
The only way I have found to clear the problem is to execute the INSERT from SQL Studio and just wait for it to complete. Then everything returns to normal, until it hangs again. The application that commands the INSERT can not be made to just wait for 90 seconds, so I need to know how to eliminate the intermittent delay.
What could be causing this problem, and how can it be corrected?

View 5 Replies View Related

Faulty Insert Into Sql Express Database

Apr 2, 2007

Hello All,

I have an InfoPath form that submits data to a .NET webservice. The webservice parses the infopath form and inserts the data into a database. The database is SQL Server EXPRESS.

I have however run into some problems. The InfoPath form is within SharePoint and I noticed that when I submit the data, it only shows up in the database as a new record 9 out of 10 times. That is for every 10 submissions of the form, there are only about 9 new records in the database. The ID field (an autoincrement field) also skips a number indicating the the transaction may have failed.

However, when I test the form on my local PC, it inserts a new record for every submission.

The webservice and database are on a remote server not my local.

Does anyone have any ideas? Could it be a deficiency of SQL Server Express?

Any ideas, answers or suggestions would be much appreciated.

View 1 Replies View Related

Insert Command In Sql Server Express Edition Using ASP.net C# Lan

Dec 31, 2007

Hi,
 i'm creating registeration form using VS2005, i want to insert values in sql server express 2005. i've coded but it is giving error plz hava a look
 for example my database name is Database1 and table is table1
and columns are name,title. 
here i'm getting error : connectionstring does not exist. 
 
code as folllows
SqlConnection connection = new SqlConnection(ConnectionString1);      
        string cmdString = "Insert into Table1(name,title)VALUES( @Name,@title)";
        SqlCommand cmd = new SqlCommand(cmdString, connection);
        cmd.Parameters.Add(new SqlParameter("@name",txtNameText));          cmd.Parameters.Add(new SqlParameter("@title",txtTitle.Text) );                        connection.Open();        cmd.ExecuteNonQuery();        connection.Close();
 
thanks in advance

View 6 Replies View Related

Insert Record Into Sql Database Express When Button Pressed

Apr 30, 2008

I'm new to asp.net and I have gotten quite far with books and such but I'm stuck at this point. I have a web form that is mostly populated with data pulled from AD and all the user does is make a selection in two drop lists and enter their initials to sign. I can't figure out how to insert a record into a table when buttin pressed. I verified that all the data types are correct so it shoud pass the info but its just not going anywhere. I've atached the code below.
Thanks,protected void accept_btn_Click(object sender, EventArgs e)
{
string adPath = LDAP://DC=********,DC=***; //Path to your LDAP directory servermembers adAuth = new members(adPath);
 string username = Context.User.Identity.Name;
string computer_name = System.Environment.MachineName;string comp_type = comptype.SelectedValue.ToString();
string install_type = installtype.SelectedValue.ToString();string date = DateTime.Now.ToString();
string email = adAuth.getemail(Context.User.Identity.Name);string agree = initials_txtbox.Text;string title = "Office";
 test_lbl.Text = (username + " " + computer_name + " " + comp_type + " " + install_type + " " + date + " " + email + " " + agree + " " + title);
 
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbParameter parameter1 = new System.Data.OleDb.OleDbParameter();System.Data.OleDb.OleDbParameter parameter2 = new System.Data.OleDb.OleDbParameter();
System.Data.OleDb.OleDbParameter parameter3 = new System.Data.OleDb.OleDbParameter();System.Data.OleDb.OleDbParameter parameter4 = new System.Data.OleDb.OleDbParameter();
System.Data.OleDb.OleDbParameter parameter5 = new System.Data.OleDb.OleDbParameter();System.Data.OleDb.OleDbParameter parameter6 = new System.Data.OleDb.OleDbParameter();
System.Data.OleDb.OleDbParameter parameter7 = new System.Data.OleDb.OleDbParameter();System.Data.OleDb.OleDbParameter parameter8 = new System.Data.OleDb.OleDbParameter();conn.ConnectionString = "provider=System.Data.SqlClient; Data Source=./SQLEXPRESS;AttachDbFilename=|DataDirectory|/software.mdf;Integrated Security=True;User Instance=True";
command.Connection = conn;
command.CommandText = "INSERT INTO [download_table] (username, computer_name, computer type, install type, date, email, agree, title) VALUES (?,?,?,?,?,?,?,?)";command.CommandType = CommandType.Text;
parameter1.ParameterName = "username";parameter1.DbType = DbType.String;
parameter1.Value = username;
command.Parameters.Add(parameter1);parameter2.ParameterName = "computer_name";parameter2.DbType = DbType.String;
parameter2.Value = computer_name;
command.Parameters.Add(parameter2);parameter3.ParameterName = "comptype";parameter3.DbType = DbType.String;
parameter3.Value = comp_type;
command.Parameters.Add(parameter3);parameter4.ParameterName = "installtype";parameter4.DbType = DbType.String;
parameter4.Value = install_type;
command.Parameters.Add(parameter4);parameter5.ParameterName = "date";parameter5.DbType = DbType.DateTime;
parameter5.Value = date;
command.Parameters.Add(parameter5);parameter6.ParameterName = "email";parameter6.DbType = DbType.String;
parameter6.Value = email;
command.Parameters.Add(parameter6);parameter7.ParameterName = "agree";parameter7.DbType = DbType.String;
parameter7.Value = agree;
command.Parameters.Add(parameter7);parameter8.ParameterName = "title";parameter8.DbType = DbType.String;
parameter8.Value = title;
command.Parameters.Add(parameter8);
try
{
conn.Open();
command.ExecuteNonQuery();
}catch (Exception ex)
{
}
finally
{
conn.Close();
}
}

View 3 Replies View Related

Not Able To Insert Record In SQL Server 2005 Express Edition

Jul 29, 2006

Hello,

In a web application of VS.Net 2005 I am able to insert records in both the database SQL 2000 & 2005

In a windows application of VS.Net 2005 I am able to insert record in SQl 2000 but I am not able to insert record in SQL 2005 Express Edition.

Please help Me I tried very Much but not succeded yet.

Thanks in Advance

View 6 Replies View Related

Sql 2005 Express: Cant Insert Records Due To Log File Being Full

Mar 27, 2008

im having an issue with sql server 2005 express on a test box. in recent testing, ive encountered errors with the database that i havent encountered before. im trying to use an app to add records to some tables in a database, 1 record at a time. but in doing so i get this



"The transaction log for database 'ScanOrder2' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"





with every insert of a record.






this process was working fine 2 days ago and i cant find anything
thats been changed.
- i deleted the database and tried restoring it from a backup that is
known to work, but still has the issue
- the recovery model is set to simple. every night a batch script is
run to truncate the log file in case there is a transaction that uses
a lot of records
- i can insert and delete records manually with no problems (ie going
in through SSMSE and highlighting a row and deleting it or adding
random data) but i still cant add it through the app
- the log file is set to autogrow unrestricted at 10 megs at a time.
- i even tried setting the log file to 500 megs starting size, and
still couldnt even insert 1 record
- when i check the log_reuse_wait_desc, it says NOTHING, when not
running and while running.


is there any other setting i could be missing that might attribute to
this?

View 7 Replies View Related

How Do I Call A Stored Procedure To Insert Data In SQL Server In SSIS Data Flow Task

Jan 29, 2008



I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks

View 6 Replies View Related

Edited Data Won't Propagate Back To The Database (VB 2005 Express &&amp; SQL Server 2005 Express)

Dec 11, 2006



Hi,

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

Me.myTableTableAdapter.Update(Me.myDatabaseDataSet.myTable)

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.

Thanks,
Ieyasu

View 6 Replies View Related

SQL Server 2014 :: How To Insert CSV Data Into DB Where Some Data Don't Have Double Quotes

Aug 11, 2015

Example of data in CSV are as follows:

"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS

[Code] ....

View 5 Replies View Related

Insert Arabic Text To Sql Express 2005 (windows Application)

Jul 17, 2007

Hi,

I need your help guys. Am devolving a windows application by c#. I have problem of insert Arabic text to sql express 2005. I always get ???? instead of the Arabic letters. I change the collation of the database field to windows collation and choose Arabic but still I get the same problem.

Your help is appreciated

View 7 Replies View Related

Where Can I Find The Import Data/Export Data Options If I Only Have The SQL Server Management Express Studio?

Oct 4, 2007



Hi all,

It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.

Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?

Your prompt response is greatly appreciated.

Thanks!!
Tram

View 8 Replies View Related

System.Data.ConstraintException Visual Basic 2005 Express / SQL 2005 Express

Aug 18, 2006

This problem only occurs after deployment, not when debugging. In the table I am having a problem with I have an ID field designated as the primary key with the identity increment set to 1 and the identity seed set to 1. There is no data in the table when deployed. I can add records to the datagridview control but when I try updating the dataset I get this error indicating that the ID field already has a value of 1 present. If I close the application and re-start it, the exception no longer occurrs when I update the dataset. I am including the code to update the tables incase something is wrong there. Any suggestions?

Private Sub UpdateMemberTable()

Me.Validate()

Me.TblMembersBindingSource.EndEdit()

Me.TblMembersTableAdapter.Update(Me.ChurchFamilyDataSet.tblMembers)

Me.TblMemberDependentsBindingSource.EndEdit()

Me.TblMemberDependentsTableAdapter.Update(Me.ChurchFamilyDataSet.tblMemberDependents)

Me.TblMemberContributionsBindingSource.EndEdit()

Me.TblMemberContributionsTableAdapter.Update(Me.ChurchFamilyDataSet.tblMemberContributions)

End Sub

View 3 Replies View Related

Insert Data To A Table Where Select Data From Another SERVER

Oct 8, 2007

I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name)
Dim selectSQL As StringDim con As SqlConnection
Dim cmd As SqlCommand
Dim reader As SqlDataReader
Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;")
Dim username As String
username = Request.QueryString("username")
selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con)
con.Open()
reader = cmd.ExecuteReader()
lbl.Text = selectSQLCatch ex As Exception
lbl.Text = ex.Message
Finally
If (Not con Is Nothing) Then
con.Close()
con.Dispose()
End If
End Try
End Sub
'------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
May i know that how do i retrieve data from  [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password
 

View 1 Replies View Related

String Or Binary Data Would Be Truncated. I Get This Error When Entering Data Using Sql Server Management Studio Express.

May 21, 2008

String or binary data would be truncated. I get this error when entering data using sql server management studio express.

I am not running a sql to insert or update the table. This is through the EDI.

The data type is varchar(100). I enter one character and it errors on me. So this isn't a string being too long problem.

Any ideas?



View 2 Replies View Related

Upgrade MSDE To SQL Express Former Data Hidden New Data Visible

Dec 27, 2006

After my upgrade from MSDE SP 4 to SQL express (backup and restore) my application will not see the data any more.

If I use my application (VB Dot.NET 1.x ; DB= SQL Express) to add new data (rows) this new rows are visible.

Both old and new datas are seen in SQL management studio express.

If i add rows with the SQL management studio express the appliction will not see this rows.

In both cases I connect with sa - account. (Administrator even doesn't work). I search for any restrictions on row basis but i wonder why the management tool display all rows.

Any ideas!

View 1 Replies View Related

[SQL 2005 Express] How Do I Load Fixed Width Per Row Flat File? Bulk Insert Possible?

May 14, 2007

I can't use DTS nor DTSwizard as I need to put it in a .sql and run it through a command line via .bat file (it's more for the users).

Each row ends with an EOL character, the fields are all fixed width, but I have a little problem here, some rows are empty but just with a EOL character.

How shall I go about it?

many thanks! :D

View 2 Replies View Related

BULK INSERT Format File Problems With SQLNUMERICAL In SQL-Server 2005 Express

May 24, 2006

I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.

The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:MDataOrder_F.txt'
WITH
(
FORMATFILE = 'E:MDataOrder_F_format.txt'
)

and written the following format file...

9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "
" 17 AGE_KEY ""

However... actually running this gives the following error...

Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

or

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...


Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

or

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


Also - there was the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,

Any thoughts on this would be greatly appreciated...

Thanks!

View 1 Replies View Related

Shortcut To Insert New GUID Into Uniqueidentifier Cell In MSSQL Management Studio Express?

Jul 15, 2006

Hi,

anyone knows if there's an easy way to insert new GUIDs into uniqueidentifier cells when editing data in table on Management Studio Express? or Visual Studio 2005 Server Explorer? I can't find a shortcut, and copying from external tools is pain..

I suppose it's possible to build a VB macro in Visual Studio, would it work when editing table data?

Thanks in advance!

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved