string strSQL = "INSERT INTO Members (Username, Password, Email) values ('lol', 'lol', 'lol')";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("app_data/members.mdb")); System.Data.OleDb.OleDbCommand com = new System.Data.OleDb.OleDbCommand(strSQL, conn);
conn.Open();
com.ExecuteNonQuery();
conn.Close();
The problem here is that it complains about the: "INSERT INTO Members (Username, Password, Email) values ('lol', 'lol', 'lol')";
When i use the same query at microsoft access to the same database it works fine but not in visual web developer (language: C#).
I have no idea what's the problem here, please help me!
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here: Function Main() on error resume next dim cn, i, rs, sSQL Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>" set rs = CreateObject("ADODB.Recordset") set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount sSQL = rs.Fields(0).value cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution rs.MoveNext Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script: - The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Table 1: AddressBook Fields --> User Name, Address, CountryCode
Table 2: Country Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Hello, I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls. I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems ( @eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar, @eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar, @outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar, @q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar, @levent nvarchar, @eventdate nvarchar, @eventtime nvarchar ) AS UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile, q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn, outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3, qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName, CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime WHERE (ePRN = @ePRN) and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text); cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text); ((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text); cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text); cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text); cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text); So is there any way to do it better than this way ?? Thank you
My web project (ASP.NET 2.0 / C#) runs against sql server 2000 and uses the System.Data.SqlClient.using System.Data.SqlClient; I use System.Data.SqlClient.SqlConnection and System.Data.SqlClient.SqlCommand to make the connections to the database and do selects and updates. Is it correct to continue to use these against SQL Server 2005? I ask because I made a connection string (outside of .Net) for SqlServer 2005 using the SQL native provider and it had the following - Provider=SQLNCLI.1 and any connection strings I had made (also outside of ASP.NET) fro SQL Server all used Provider=SQLOLEDB.1. This is why I wondered if there is a different SqlClient in .Net 2.0 for SQL Server 2005? Cheers Al
hi - I am developing locally using .Net 2.0/Access but just recently started trying out SQL Server Express. I have deployed my application to a host who provides 2.0 but as would be expected only MSDE/SQL server 2000. Now if I'd switch completely to SSE, would my SQL queries (which are pretty simple) work on MSDE or SQL server 2000? The only thing I see as more sophisticated sql queries would be the built-in 2.0 Roles/Membership functions... Do you think they would run smoothly on MSDE/SQL 2000?Finally... since the database is already done locally, is there any way (a stored procedure for example) to copy it's scheme if I'd create a new DB on my host's SQL server?
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks geniuses.
Having an issue where I can connect to a remote SQL Database server via enterprise manager, however using the exact same db credentials, I can't connect via the .NET SqlConnection object...
Anyone have this error? Did you ever figure it out?
The error I get is: "SQL Server does not exist or access denied."
Again... I'm using the EXACT same user name, password, port number. I've setup an alias in Client Network Utility....
The SQL Server Enterprise is running on the exact same PC as the code I'm trying to execute.
try { con = new SqlConnection("server=localhost; uid=sa; pwd=123; database=TaskManagement"); cmd = new SqlCommand("INSERT INTO TASK_TYPE (name) VALUES ("+name.Text+")", con);
Hello, I am new to ASP.net. I just installed MS Sql Server and Visual Web Developer 2005 Express Edition. Can any help with the code to connect to SQL server?
Does anyone know a workaround for the code page translation that occurs if you use bcp to transfer data in and out of SQL Server. I have a table that I am losing certain characters in the transfers.
SQL Server 7.0 has the -C code page specifier added to BCP to address this but I am running 6.5 currently.
I am trying to perform a simple update to a SQL Server database table and I can't figure out why this simple UPDATE command doesn't work. I am performing the same thing in another page for owner information and it works just fine. It seems that some of my code won't work even though the syntax is correct and there is no reason for it not to work.
I have hardcoded text for some of the fields and the UPDATE code below works but when I try to use the information that may be in the Textbox, the code won't do the UPDATE. Please help me figure out what is going on.
Thanks
Sub btnUpdate_Click_1(sender As Object, e As EventArgs) Dim UserCode as String Dim PropertyCode as String Dim UnitCode as String Dim address1 as String Dim address2 as String Dim address3 as String Dim city as String Dim zip_Code as String Dim description as String Dim price_Range as String Dim state as String Dim type_Property as String Dim property_Status as String
if chkChangeState.Checked = True then state = ddl_State.SelectedItem.Value else state = lblState.Text end if
if chkPropertyType.Checked = True then type_Property = ddlPropertyType.SelectedItem.Value else type_Property = lblPropertyType.Text end if
if chkPropertyStatus.Checked = True then property_Status = ddlPropertyStatus.SelectedItem.Value else property_Status = lblPropertyStatus.Text end if
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "UPDATE [Property_db] SET [Address1]=@Address1, [Address2]=@Address2, [Address3]=@Address3, [City]=@City, [State]=@State, [Zip_Code]=@Zip_Code, [Type_Property]=@Type_Property, [Description]=@Description, [Property_Status]=@Property_Status, [Price_Range]=@Price_Range WHERE ([Property_db].[Code] = @Code) AND ([Property_db].[Prop_Code] = @Prop_Code) AND ([Property_db].[Unit_Code] = @Unit_Code)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_code.ParameterName = "@Code" dbParam_code.Value = UserCode dbParam_code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_code) Dim dbParam_prop_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_prop_Code.ParameterName = "@Prop_Code" dbParam_prop_Code.Value = PropertyCode dbParam_prop_Code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_prop_Code) Dim dbParam_unit_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_unit_Code.ParameterName = "@Unit_Code" dbParam_unit_Code.Value = UnitCode dbParam_unit_Code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_unit_Code) Dim dbParam_address1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_address1.ParameterName = "@Address1" dbParam_address1.Value = txtAddress1.Text dbParam_address1.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_address1) Dim dbParam_address2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_address2.ParameterName = "@Address2" dbParam_address2.Value = txtAddress2.Text dbParam_address2.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_address2) Dim dbParam_address3 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_address3.ParameterName = "@Address3" dbParam_address3.Value = txtAddress3.Text dbParam_address3.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_address3) Dim dbParam_city As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_city.ParameterName = "@City" dbParam_city.Value = txtCity.Text dbParam_city.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_city) Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_state.ParameterName = "@State" dbParam_state.Value = state dbParam_state.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_state) Dim dbParam_zip_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_zip_Code.ParameterName = "@Zip_Code" dbParam_zip_Code.Value = txtZipCode.Text dbParam_zip_Code.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_zip_Code) Dim dbParam_type_Property As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_type_Property.ParameterName = "@Type_Property" dbParam_type_Property.Value = type_Property dbParam_type_Property.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_type_Property) Dim dbParam_description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_description.ParameterName = "@Description" dbParam_description.Value = txtDescription.Text dbParam_description.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_description) Dim dbParam_property_Status As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_property_Status.ParameterName = "@Property_Status" dbParam_property_Status.Value = property_Status dbParam_property_Status.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_property_Status) Dim dbParam_price_Range As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_price_Range.ParameterName = "@Price_Range" dbParam_price_Range.Value = txtPriceRange.Text dbParam_price_Range.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_price_Range)
Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try
'if Not rowsAffected then Response.Redirect("./editproperty.aspx") 'end if End Sub
Is there anyway for me to trace source of the error no?
The error msg is as below: NSQL 73301: EXcProd does not exist (nsp_Archive_Table)
EXcProd is the database, where nsp_Archive_Table is the store procedure. Actually, nsp_Archive_Table do exist in the database but the system keep saying that it's not.
Hello, maybe you are interested in this. I wrote a java applet on www.sqlinform.com which is a SQL Code beautifier / formatter. It is for all kind of SQL (DB2, ORACLE; Access, Informix, etc). The only thing you need is a Java Runtime Environment (which should be available in most cases). You can beautify SQL statements out of program code and format them for Java, ASP, VB, PHP. Regards GuidoMarcel
I am trying to connect a production SQL Server 2005 which is under firewall through an Application written in ASP.Net 1.1 and I get error "SQL Server not available or Access Denied".
I am able to connect to the Server through SQL Mgr also using the same Code and Web.Config file I am able to Connect to the SErver using ASP.Net 2.0.
I am able to run the application for a SQL Server 05 which is in the same domain as of the Application Server.
My Application is not working only when I use ASP.Net 1.1 code for the Remote SQL Server 05 which is under Firewall. But for the same Server ASP.Net 2.0 works fine.
I have searched a lot for the problem and port No, DBNETLIB update is also tried.
Please let me know if anyone has came across such problem or knows the possible cause of the problem.
For the above function I added reference to the System.Data . The report works as desired when I execute in the VS 2005 IDE but when I deploy the report on the reporting server and execute it from there the function doesn€™t execute and returns me #Error.
Can any one provide some insight into it to as to how i can resolve this issue.
There are procs to add linked servers, and procs to add and delete linkedserver logins, but I dont see a proc to delete the actual linked server definition. And yet EM will drop the definition of a linked server. So the obvious question is "How do you code the dropping of a linked server?
I'm a newbee to SQL Server. I have a very simple question to you experts: How should I code a literal of the "datetime"-Datatype? For Example in the VALUES-clause of an SQL-statement. I have tested several "formats" ('20.04.2006 11:15:00' with an 4-digit year enclosed in single apostrophes) but all i earned is an exception!
Any help very appreciated!
Thanks in advance and best regards
Reiner
PS.: I'm using a german-localized database (thus the date-format dd.MM.yyyy).
Hello everyone, this post will be a bit lengthy, so please bear with me. I've been programming asp.net for abou 2 years. I'm not a master by any means, and I do things like I learn from books. Here is my standard way of connection to my mssql database (which is on the same server as my site, so I just use "localhost") Dim objConnection As New SqlConnection(Application("dbstring")) Dim strSQL As String = "SELECT * FROM myTable WHERE this='that'" Dim objCommand As New SqlCommand(strSQL, objConnection) Dim objDataReader As SqlDataReader
Do While objDataReader.Read() = True 'Do what I need to do with the data Loop
objDataReader.Close() objConnection.Close()
objConnection.Dispose()Here's my issue. Our website gets this error 2-4 and as many as 6 or 7 times a day: General network error. Check your network documentation.Line ###: objConnection.Open()I use the method I wrote above for getting data on almost every page in the site. Its a HUGE site, but I've made sure that I close all my connections. This is my global connection string: Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) Application("dbstring") = "Server=WWW1;database=###;uid=#####;password=#####;Connect Timeout=300;Connection Reset=False;Pooling=False;Max Pool Size=75000;" End Sub Here's my web.config file:<configuration> <system.web> <customErrors mode="Off"/> </system.web></configuration>... This server has only one website running on it, its a dedicated server. Is there something in my code causing this error to happen so much? We have to restart the sql server everytime this error happens for it to go away. Its very disruptive to the website.
Hi Smart Folks, My SQL Server-based app will require generating a few tables at run-time, and, possibly, a new small database. Again, this is at run-time. Any advice on this? Should I build the functionality from the app, having my code generate all of the commands on the db to create what I need, or should I rely on SQL Server functionality (Scripts? SPs?) to do so. I am sure there is trade-off in one appraoch versus the other - I could use a tour of the trade-offs. Thanks all! DF
I have a problem. I m working on "light weight sql server" project.
and i want to execute .sql file through vb.net code with the help of sqldmo library and sqlns namespace. but i donot know any method to directly execute the .sql file. i am successfully making the full script of select database of sql server.
I wrote code to archive Data.I created a table that set the maximum number of records to archive and delete and loop until finished or a set a flag in a table to stop archiving. I was able to limit the number of records to commit.
Hi,I want to create a tool that run over Windows Server 2003 and checksthe sql server 2000 edition (Enterprise, Standard, Personal,Developer).I know I can do from the query analyzer - "Select @@version".But how can I do it from code, is there a WMI API that can tell mewhat's the edition?Thanks,Koby
I have an ASP.NET application that access an SQL Server 2005 DB. I develop my C# code using MS Visual Studio. I was happily developing and testing for many months on my machine and then I lost a hard disk. I've replaced the hard disk and reinstalled everything and am getting back to my work. Now though when I try to open the database in my code I get an error saying it was unable to connect to the database perhaps due to SQL Server 2005 not being configured for remote access.
I can see the database using MS SQL Server Management Studio Express and open it. The properties shows the box checked to allow remote access. I restored the database from another hard drive so it should be no different.
I dont' recall having to do anything special the first time to access my database from my code. If I did I forget. What could be causing this? My code hasn't changed so the connection string is the same as is the location of the database.
i have created a vb.net application. i have a database (sql server 2005)with user and password which are salted. I want to use the login form of vb.net to login in to the application.(how to code the comparision string?) the username and password will be put in text boxes.(how to form the connection to the sql database?) how to do the coding for this?? plz help..
