?
Solved

Add data in SQL using VB 2005 EE *** Urgent ***

Posted on 2007-03-21
10
Medium Priority
?
315 Views
Last Modified: 2013-11-27
Experts,

I am trying to build a simple application in VB 2005 EE and SQL Server 2000. I am new to VB 2005 EE. However all I want is, a form with 2 labels, 2 text box and 2 picture boxes. Upon clicking the picture boxes, all data have to be stored in the database. However somehow I managed to add a new database source using the "Data" option. But I dont know how to add the data to sql. Kindly help me on the same.
0
Comment
Question by:Manju
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 6

Author Comment

by:Manju
ID: 18771353
Team,

Can anyone help me on this. I need to create a basic application.
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 18777592
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 18777735
Dim cn As SqlConnection

cn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                      "Initial Catalog=Northwind;Trusted_Connection=Yes;"
cn.Open()
Dim cmd As SqlCommand = cn.CreateCommand()

cmd.CommandText = "Insert into YourTable  (YourField1, YourField2)  values('BLAH', 'Blue')"
cmd.ExecuteNonQuery()
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Author Comment

by:Manju
ID: 18781326
Hi,

I tried this code.

  Dim status

        status = "1"

        Dim cn As SqlConnection

        cn.ConnectionString = "Provider=SQLOLEDB;DataSource=BLRXTRACKDB; Catalog=iRESOLVE;Userid=ASGBot; Password=ASGPass;Trusted_Connection=Yes;"
        cn.Open()
        Dim cmd As SqlCommand = cn.CreateCommand()

        cmd.CommandText = "Insert into Smiley  (Date, NTlogin, CaseNumber, CustomerNumber, Status)  values(label3.text, label6.text, textbox2.text, textbox1.text, status )"
        cmd.ExecuteNonQuery()

However I am getting an error -

Null Reference Exception was unhandled.
Object reference not set to an instance of an object in this line,

        cn.ConnectionString = "Provider=SQLOLEDB;DataSource=BLRXTRACKDB; Catalog=iRESOLVE;Userid=ASGBot; Password=ASGPass;Trusted_Connection=Yes;"
0
 
LVL 6

Author Comment

by:Manju
ID: 18783862
Joseph,

Thanks so much for your help.
I have made some changes on ur code and this one works perfectly fine.
However what I want is this. 2 text boxes will be filled by the user and 1 label is date and the other one is nt login. Kindly help me rectify this code.

Code:
 cn.ConnectionString = "Server=BLRXTRACKDB; Database=iresolve;User id= ASGBot; Password= ASGPass;"
        cn.Open()
        Dim cmd As SqlCommand = cn.CreateCommand()

        Dim date1 As Date
        date1 = Label3.Text
        Dim login
        login = Label6.Text
        Dim casenumber
        casenumber = TextBox2.Text
        Dim customernumber
        customernumber = TextBox1.Text
        Dim status
        status = "1"

        cmd.CommandText = "Insert into Smiley (date, NTlogin, CustomerNumber, caseNumber, Status)  values ('date','Ntlogin', 'CustomerNumber', 'CaseNumber', 'Status')"
        MsgBox("Updated")
        cmd.ExecuteNonQuery()

        cn.Close()

Using the above code, I am able to save data in the backend, however its saving as same. Its not getting the data what the user is entering. Need your help pls.
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 18783915
Your connection string is wrong:

Remove this "Provider=SQLOLEDB" in your connectionstring

Here is a useful link

http://www.connectionstrings.com/?carrier=sqlserver

SqlConnection (.NET)
 
Standard Security
 
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
 
  COPY TO CLIPBOARD
Standard Security alternative syntax
This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.  
Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
 
  COPY TO CLIPBOARD
Trusted Connection
 
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
 
  COPY TO CLIPBOARD
Trusted Connection alternative syntax
This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.  
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
 
Use serverName\instanceName as Data Source to use a specific SQL Server instance. Please note that the multiple SQL Server instances feature is available only from SQL Server version 2000 and not in any previous versions.  
 


0
 
LVL 6

Author Comment

by:Manju
ID: 18783917
Joseph,

Thanks so much for your help.
I have made some changes on ur code and this one works perfectly fine.
However what I want is this. 2 text boxes will be filled by the user and 1 label is date and the other one is nt login. Kindly help me rectify this code.

Code:
 cn.ConnectionString = "Server=BLRXTRACKDB; Database=iresolve;User id= ASGBot; Password= ASGPass;"
        cn.Open()
        Dim cmd As SqlCommand = cn.CreateCommand()

        Dim date1 As Date
        date1 = Label3.Text
        Dim login
        login = Label6.Text
        Dim casenumber
        casenumber = TextBox2.Text
        Dim customernumber
        customernumber = TextBox1.Text
        Dim status
        status = "1"

        cmd.CommandText = "Insert into Smiley (date, NTlogin, CustomerNumber, caseNumber, Status)  values ('date','Ntlogin', 'CustomerNumber', 'CaseNumber', 'Status')"
        MsgBox("Updated")
        cmd.ExecuteNonQuery()

        cn.Close()

Using the above code, I am able to save data in the backend, however its saving as same. Its not getting the data what the user is entering. Need your help pls.
0
 
LVL 15

Accepted Solution

by:
JackOfPH earned 2000 total points
ID: 18784146
Replace this:
cmd.CommandText = "Insert into Smiley (date, NTlogin, CustomerNumber, caseNumber, Status)  values ('date','Ntlogin', 'CustomerNumber', 'CaseNumber', 'Status')"

with this one:

cmd.CommandText = "Insert into Smiley (date, NTlogin, CustomerNumber, caseNumber, Status)  values ('" & date1 & "','" & Ntlogin & "', '" & CustomerNumber & "', '" & CaseNumber & "', '" & Status & "')"
0
 
LVL 6

Author Comment

by:Manju
ID: 18785622
Bingo! Thanks Joe. I'll have more questions moving forward, and I want your expertise to help me learn.

Thanks.
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 18790049
I am glad I help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question