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

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.
LVL 7
ManjuIT - Project ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ManjuIT - Project ManagerAuthor Commented:
Team,

Can anyone help me on this. I need to create a basic application.
0
JackOfPHCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ManjuIT - Project ManagerAuthor Commented:
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
ManjuIT - Project ManagerAuthor Commented:
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
JackOfPHCommented:
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
ManjuIT - Project ManagerAuthor Commented:
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
JackOfPHCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ManjuIT - Project ManagerAuthor Commented:
Bingo! Thanks Joe. I'll have more questions moving forward, and I want your expertise to help me learn.

Thanks.
0
JackOfPHCommented:
I am glad I help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.