Error With Names Like Georgia's Way While Inserting Into A Database
May 14, 2008
Hello All,
i am trying to insert some values into a table in sql database. i keep getting error saying incorrect syntax near 'S'. i fired up my debugger and found that one of the row contains name like Georgia's way. i am getting error at the "S" in georgia's way. how can i fix that. here is my code for inserting the values in to the table
SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString);
mysqlcon.Open();
foreach (DataRow dr1 in objDataSet.Tables[0].Rows)
{
String sqlinsert = String.Format("insert into Det values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},{8},'{9}','{10}','{11}','{12}')"
, dr1[0].ToString()
, dr1[1].ToString()
, dr1[2].ToString()
, dr1[3].ToString() -- this is the column where georgia's way is
, dr1[4].ToString()
, dr1[5].ToString()
, dr1[6].ToString()
, Convert.ToDecimal(dr1[7].ToString())
, Convert.ToDecimal(dr1[8].ToString())
, dr1[9].ToString()
, dr1[10].ToString()
, dr1[11].ToString()
, dr1[12].ToString());
new SqlCommand(sqlinsert, mysqlcon).ExecuteNonQuery();
LabelImport.Text = " Row Inserted";
}
mysqlcon.Close();
can some please help me out.
Thanks a lot
I'm trying to perform an insert on a table from another table, but the fieldnames are different. The data is similar, but there are less fields as well. Does anyone know how to accomplish this? All the material I've read on the Internet so far pertaining to Insert statements, has all the column names matching up.
/*Code below raises following errors:Server: Msg 245, Level 16, State 1, Line 6Syntax error converting the varchar value 'a' to a column of data typeint.*/create table #x (i integer, c char(1))create table #y (c char(1), i integer)insert into #x VALUES (1, 'a')insert into #y SELECT * from #xdrop table #xdrop table #y--CODE COMPLETEIs there a way to use the column names as the basis for the insert asopposed to column position? The key here is that I DO NOT want toSPECIFY COLUMN NAMES (i.e. 'insert into #y (i,c) SELECT * from #x')Regards,Ty
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...
The error occurred in D:HostingkpstoolinsertPage.cfm: line 132 130 : datasource= "kpstool_accesscf_jobs"> 131 : INSERT INTO Jobs (Position, Needs, Necessary) 132 : VALUES ('#form.Position#','#form.Needs#','#form.Necessary#') 133 : </cfquery> 134 : <cfoutput>
This is the code for the form that this code is referring to: <form action="insertpage.cfm" method="post" name="Form" id="Form"> Position: <input type="text" name="Position" size="25" maxlength="25"> <br> <input type="hidden" name="Position_required" value="You must enter position"> <br> Needs: <input type="text" name="Needs" size="25" maxlength="25"> <br> Necessary:
Hi, Im struggling with this insert statement, I want to use with a AJAX validation Post Form page. Its quite straght forward, if a search query returns null the insert these values. The search query does work, what I mean by that is that txt field values seem to pass for search but not insert. Any help out there cheers Paul if (RowCount == 0) {String strSQL = "INSERT INTO Mail_List (FirstName, Email) VALUES( @FirstName, @Email )";
try {mySqlConn = new SqlConnection(strSqlConn); mySqlConn.Open();SqlCommand cmd = new SqlCommand(); cmd = new SqlCommand(strSQL, mySqlConn);cmd.Parameters.AddWithValue("@FirstName", Request.Form["FirstName"]);cmd.Parameters.AddWithValue("@Email", Request.Form["Email"]); cmd.ExecuteNonQuery(); lblStatus.Text = "Registration Successful"; }
Hi, Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error : Must declare the scalar variable "@BookMarkArrayA". when it reaches the .insert command, I've tried using a local variable temp in place of the array element and .ToString , but still get the same error This is the code : Public Sub NewCustomer() Dim temp As String = " " Dim ID As Integer = 1 'Restore the array from the view state BookMarkArrayA = Me.ViewState("BookMarkArrayA")
temp = BookMarkArrayA(6) Dim Customer As SqlDataSource = New SqlDataSource()
just starting with sql server, thru the sql express edition, before i get to far wondering if it is ok to use the same database names, specifically the ASPNetDB.mdf in different web applications, or will this cause problems down the line,
ie will have different web site applications running on IIS and want to use the aspnet_regsl wizard,
also wondering if this has any implicaitons when publishing web application to web servers, are any of the local sql express server settings passed on to the web host provider, read about machine.config and if i understand i should put all the settings in the web.config file via the VS20005 ide.
also will it cause any problem inside the sql express magement console,
Have a view that has to reference a table in another database on the same server. This isn't the problem. I am just using Databasename..tablename in the from statement. Get the results required in testing.
The problem is that in production the table name has a space character. i.e. 'accounting info' would be the table name. I have not been able to figure out the proper syntax to capture this properly to reference in the from statement.
I have tried enclosing the name in ' ', " ", [], (), {} and just about every combination I can come up with.
Any help in this would be great, and changing the databse name isn't an option at this time.
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
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.
Hello, I have ran into a problem when i'm getting table names in c#. I have tried the sql command: select name from sysobjects where type='U' order by name So it returns the names without a problem. But when I try to access to some tables using their names (to retrieve the columns) I sometimes get the error "Invalid object name" following by the name of the table. So I realized the if the table was not under dbo user then i would get this error. for example: "select name from ContactCreditCard" gives me error because in database management tool it is shown like this: Sales.ContactCreditCard How can i fix this problem? How to get that prefix of the tables?
Hello, { Merry xmas to all few hours early but hey !! }
I'm trynig to create an application that will connect to any database that i specify and return the colums names within any table that i specify.
I have done the code that establishes the connection but im a bit stumped on how to get the colum names from a tabel and was wondering if anybody had any sourse code for this in VB.
When connecting to an SQL Server (v7 in this case) the log file shows that old and deleted databases are still being opened as part of the process. Further, these db names are now reserved and cannot be reused, even thought I've deleted them.
Is there any way I can purge all traces of these deleted db names?
You might have guessed, I'm a newbie, which is why there are a few dozen deleted DBs.
I have a query return all the names of the table in my database via this code:
SELECT MSysObjects.Name FROM MSysObjects WHERE (((Left([Name],4))<>"MSYS"));
I need to further refine the search fitting in the critera LIKE 'qry_tbl%' I dont know how to fit this in .. i tried fitting it in to the end, but nothing comes up.. I also tried querying the results of this query with another query :-P but that didnt work either .. any ideas?
In essence i need all the table names in my DB that start with 'qry_tbl' to be in a list for me..
I create a user then I create a database and assign my newly created user as owner
When I login as this new user everything works fine BUT my newly created user can still see other database names even though he is not allowed to use them.
How can I prevent him from seeing other database names.
We have recently copied a database from one machine to another. On the old machine, when we access the tables we do not need to use the username.tablename convention to query them. On the new box we do.
For example, to query a table called Page we would nee to do this on the new box.
SELECT * FROM webdev.page
unfortunately all the code is written, without the username prefix. Is there a way to not use the username prefix?
Thank you for your help...sorry for the newbie type question.
ho w do i get all the database names from my local machine which are present in the sql sevrer 2005 i also need how do i get the names of all the tables,views,stored procedure names in the particular database
Hi SQL server experts, I'm trying to restore from a backup file. I need to restore the datafile and logfile to different names. I modified the options page, changed the 'Restored As' file name. I clicked on the General page and Clicked on the Options page again, the old values came back. How do I handle this? Thanks in advance. Pingx
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Hi, I am trying to do a simple ftp using DTS. I am using a Dynamic properties task as the file name is a bit complicated eg - f2368fd1.ext.040330.2 where 040330 is according to the date and the .2 is the second file for the day. When i run the task, it fails giving me the error "Invalid format for string source names"
I then tried to use a simple file name such as TD.txt as a constant in the Dynamic properties task but still it fails. Any ideas ??
I am trying to form a replication system but at the very beginning i couldn't pass an obstacle. While trying to create the Replication it says i have to change the user which starts the SQL Agent because the current starter user account is a system account and this will make the replication between servers fail. "SQL Server Agent on OZN currently uses the system account, which causes the replication between the servers fail. In the following dialog box, specify another account for the service startup account." I change it in the properties dialog box of the SQL Server Agent. The new account is the one I formed and granted accordingly. But it gives the following error when I try to apply the changes. " Error 22042: xp_SetSQLSecurity() returned error -2147023564, 'No mapping between account names and security IDs was done' "
I tried many things, searched in the net, changed the owner of the database, applied new accounts, many grants, applied service pack 4, etc...
If anyone helps it will be very much appreciated. Thanks in advance...
hey guys I got an error when trying to insert an SQL statement. basically it when I try to insert into a unique ID column I think error is: Compiler Error Message: BC30516: Overload resolution failed because no accessible 'New' accepts this number of arguments.Source Error:
Line 51: objCmd = New SQLCommand(strSQL1, SqlConn) Line 52: Line 53: objCmd.Parameters.Add(New SqlParameter("@CustomerID")) Line 54: objCmd.Parameters("@CustomerID").Value = UserID Line 55: objCmd.Parameters.Add(New SqlParameter("@CompanyID", _Source File: C:Prototypeofficial1html icketsystem.aspx Line: 53
Dim strConnection as string = ConfigurationManager.ConnectionStrings("GeolinkSupportdb").connectionstring Dim sqlConn = New SqlConnection(strConnection) dim mu = Membership.GetUser() dim UserID = mu.ProviderUserKey.ToString()
Sub Page_Load(sender as Object, e as EventArgs)
If Not IsPostBack() then '1. Create a connection Dim sqlConn = New SqlConnection(strConnection) '2. Create the command object, passing in the SQL string Const strSQL as String = "SELECT IssueID, IssueName FROM Support_Issue;" sqlConn.Open() Dim myCommand as New SqlCommand(strSQL, sqlConn) '3. Create the DataReader
Dim objDR as SqlDataReader objDR = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'Databind the DataReader to the listbox Web control TicketIssue.DataSource = objDR TicketIssue.DataTextField = "IssueName" TicketIssue.DataValueField = "IssueID" TicketIssue.DataBind()
'Add a new listitem to the beginning of the listitemcollection TicketIssue.Items.Insert(0, new ListItem("-- Choose an Issue --")) objDR.close() sqlConn.Close()
'assign fullname from profile and company ID to the text fields. FullName.Text = profile.firstName & " " & profile.lastName Company.Text = profile.CompanyID end if End Sub Sub Submit(sender as Object, e as EventArgs) Dim rightNow as DateTime = DateTime.Now Dim strSQL1 = "insert into Support_Ticket (CustomerID, CompanyID, Subject, Problem, Open_Date, IsClosed) Values (@CustomerID, @CompanyID, @Subject, @Problem, @OpenDate, @IsClosed);" Dim objCmd As SqlCommand objCmd = New SQLCommand(strSQL1, SqlConn)
objCmd.Parameters.Add(New SqlParameter("@CustomerID", _ SqlDbType.uniqueidentifier)) objCmd.Parameters("@CustomerID").Value = UserID objCmd.Parameters.Add(New SqlParameter("@CompanyID", _ SqlDbType.uniqueidentifier)) objCmd.Parameters("@CompanyID").Value = profile.CompanyID objCmd.Parameters.Add(New SqlParameter("@Subject", _ SqlDbType.varchar, 50)) objCmd.Parameters("@Subject").Value = Subject.Text objCmd.Parameters.Add(New SqlParameter("@Problem", _ SqlDbType.ntext)) objCmd.Parameters("@Problem").Value = TicketInfo.Value objCmd.Parameters.Add(New SqlParameter("@Open_Date", _ SqlDbType.DateTime)) objCmd.Parameters("@Open_Date").Value = rightNow.ToString("dd/MM/yyyy , HH:mm:ss") objCmd.Connection.Open() objCmd.Parameters.Add(New SqlParameter("@IsClosed", _ SqlDbType.bit)) objCmd.Parameters("@IsClosed").Value = 0 ' Test whether the new row can be added and display the ' appropriate message box to the user. Try Catch ex As SqlException objCmd.ExecuteNonQuery() Message.InnerHtml = "<b>Record Added</b> to Database<br>"
If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else
Message.InnerHtml = "ERROR: Could not add record, please " _ & "ensure the fields are correctly filled out" Message.Style("color") = "red"
End If End Try objCmd.Connection.Close() end sub
the database looks like this TicketID uniqueidentifier CustomerID uniqueidentifier Subject varchar(50) Problem ntext Open_Date datetime IsClosed bit Closed_Date datetime DeptID uniqueidentifier CompanyID varchar(50)
Hey folks, I am trying to updat a table by inserting values from another table into the parent table. The SQL being used is: "insert into "LogSchema"."_rt1182" select * from "LogSchema"."_rt557" where recordID not in (select * from "LogSchema"."_rt1182") ORDER BY TIMESTAMP" I have used the above statement and was successful, but with these two particluar tables I am getting an error message" Wrong number of values to INSERT" Please lemme know, what this means and how to correct this problem! TIA
Hi, In Ms SQL server if we create a user defined stored procedure it will be stored in the SysObjects table. simillarly the paramerter are stored in other table which is that table.
I have a database and I want to retrieve informations about all tables defined in this database. In other words I need the table list defined in database. Can you help me?
I'm currently implementing a German ERP system and I'm having aterrible time writing reports/SQL statements. The database is MS SQLServer 2000. I want to set permanant aliases for the columns that Ican use in SQL statements. Is this even possible? All of the researchI've done implies that you can only alias column names in the output ofa SQL statement.
